MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Antonio on December 05, 2001 5:51 PM

I am wondering how to change the color of the cell using a macro. For example in a range of cells I have names of different fruits and I want to highlight all the apples RED, banana yellow, etc. I know I can use an if statement, but I don't know how to highlight the cell.

I hope this make sense?

Posted by Colo on December 05, 2001 7:07 PM

In this case, I think you had better use "Select case statement".
I made sample code for you.
Please try this!

Sub Test()
'Before run this, you should be selecting the range.
Dim rngTemp As Range, intIndex As Integer
For Each rngTemp In Selection
Select Case rngTemp.Value
Case "apple"
intIndex = 3
Case "banana"
intIndex = 6
Case "orange"
intIndex = 46
End Select
rngTemp.Interior.ColorIndex = intIndex
End Sub

Posted by Dan on December 05, 2001 7:14 PM

Something like
If Selection = "Banana" then
Selection.Interior.ColorIndex = 6

Works with XL2000, not sure if the command is the same with XL97(I think so, though). HTH