Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

Changing colors in EXCEL Cells Automatically

Posted by Claude Carlson on July 30, 2001 11:47 AM
I am doing DDE exchange between EXCEL and a PLC.
The cells are automatically updated every 5 seconds
with data that represents temperature. no Problem..
No user intervention or keyboard or mouse is used to
change the data. Its automatic and works fine.
The problem::
I would like the cell background color to change to red
when the value is greater than 80, and back to green
when is is below 80... HELP!!

Re: Changing colors in EXCEL Cells Automatically

Posted by Jerid on July 30, 2001 11:52 AM

Claude, look at Format, Conditional Formatting. This should do what you are looking for.

Jerid


Re: Changing colors in EXCEL Cells Automatically

Posted by Bob on July 30, 2001 12:01 PM
I don't have any of my code samples with me now, but you should look into the Worksheet_Change() routine in Visual Basic. There have been several recent Q&As on this subject lately, but until the recent stuff is restored, I can't refer you to them. Anyway, enter Visual Basic (Tools - Macros - Visual Basic Editor), then View Project Explorer. The worksheet you are working on will be amongst the listings. Right Click on it and select View Code. A new window appears. In the upper-left pulldown select Worksheet. In the upper-right pulldown select Change (NOT Selection Change). What you will see is a subroutine that only has a beginning an end with no code. In the middle you would add the code that says essentially if target.value >= 80 turn the cell red. If target.value <80 turn the cell green. (Sorry, you will have to look up the exact code for this yourself - or record a macro to get the code and insert it into the above formula).


Re: Changing colors in EXCEL Cells Automatically

Posted by Bob on July 30, 2001 12:02 PM
Jerid's answer is obviously much shorter and easier - if it doesn't work with a DDE setup, use what I mentioned.


Re: Here it is Claude

Posted by Jim on July 30, 2001 1:21 PM
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Selection
If [A1] >= 80 Then
[A1].Font.ColorIndex = 3
End If
If [A1] < 80 Then
[A1].Font.ColorIndex = 0
End If
End With
End Sub

HTH
Jim


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.