excel macro to change color

[kurup]

Hi,

Can anybody help me out to write an excel macro that can change the color of a cell having a particular name. For eg in an excel sheet i want to change the color of all the cells to yellow that has the name "george" in it.

thanks
kurup

 

[hamzajosh]

just use

if activecell.value="george" then
With Selection.Interior
.ColorIndex = 6 '(6 is for yellow colour)
end with

It's that simple. Now you can use this in a loop to search the whole sheet if you want.

 

[xlbo]

If you need to go thru a lot of cells, the find method will almost certainly be quicker:

With Worksheets("Sheet1&quot.Range("a1:z500&quot
Set c = .Find("george", lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.colorindex = 6
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

HTH Rgds
~Geoff~

 

[aurbo]

XLBO,

How would you code this if you wish to check a specific cell from an activerow against a column range from a different sheet?

Example, I wish to change the color of Sheet1 ActiveRow column 5 only if the same value exists anywhere in Column 3 of Sheet2?

Thanks
Steve

 

[xlbo]

This should do it:

findStr = activesheet.cells(activecell.row,5).text
With Worksheets(&quot;Sheet2&quot.columns(&quot;C&quot;
Set c = .Find(findStr, lookin:=xlValues)
End With
If Not c Is Nothing Then
activecell.interior.colorindex = 6


HTH Rgds
~Geoff~