Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Auto Color Change in Excel 1

Status
Not open for further replies.

jbento

Technical User
Joined
Jul 20, 2001
Messages
573
Location
US
All,
I have a workbook with 2 worksheets. One is called "DataEntry" and the other is called "Invoice". I have a column on both with the heading title of "Name Last, First".

On the Invoice worksheet I have the following formula for the cells: =DataEntry!A5. This would take the value from the DataEntry worksheet and place it in the Invoice worksheet, therefore it is linked.

That is working fine, but if I change the text color in the DataEntry worksheet, it doesn't change the text color in the Invoice worksheet.

How can I correct this?

Any help will be appreciated. Thanks in advance.

Jerome Benton
JERPAT Web Designs
GOD Is Good All The Time!!!
 
Hi Jerome,

I've tested the following and it works...

Steps:

1) In your DataEntry sheet, assign the following range name to the input cell: inp1

2) In your Invoice sheet, assign the following range name to the corresponding (linked) cell: inv1

One method of assigning a range name:
a) Highlight the cell (or range of cells)
b) Hold down <Control> and hit <F3>
c) Type the name
d) Hit <Enter>

3) Enter the following &quot;Worksheet_Activate&quot; event for your Invoice sheet.

Private Sub Worksheet_Activate()
[inv1].Font.ColorIndex = [inp1].Font.ColorIndex
End Sub

In case you're not familiar with VBA, here are further steps...

3a) To enter the VBA Editor, hold down <Alt> and hit <F11>

3b) If your sheets are not shown on the left, use the VBA menu - choose: View - Project Explorer.

3c) Under &quot;Microsoft Excel Objects&quot;, click on the &quot;Invoice&quot; sheet.

3d) On the right side of the screen, copy and paste the above routine from here in Tek-Tips to this location in the VBA Editor.

That's all. Of course you might want to add to this, for the other input cells. Just be sure to assign the range names to each of the corresponding cells. Then, for each input/invoice cell, copy the line in the above Workbook_Activate routine - and of course change the names for each new line added.

I hope this helps :-) Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
 
Hi again,

A slight correction... In the last paragraph, it should refer to &quot;Worksheet_Activate&quot; (not Workbook)

Hope this has helped. :-)

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
 
Dale,
Thank you SOOOOOO much for your help. You were a blessing from God to help me solve this problem.
God bless,
Jerome and Patty

Jerome Benton
JERPAT Web Designs
GOD Is Good All The Time!!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top