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!!
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
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).
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.
Posted by Jim on July 30, 2001 1:21 PM
Re: Here it is Claude
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
