Change adjacent cell color

[trenny]

I know this has to be easy, but can't think of or find a solution!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I receive 1000 .csv reports every week. My job is to format them so that they look "pretty" when printed. I have been able to write/record a macro to do everything except the following:
In cell h7 on every report, there is either an "x" or no value. Based on this data, I need to change the interior color of cells h7, h6, h5, and h4 to red.
I also need to do this for cell g30 and o30, but should be able to use the same solution.
Any help is appreciated.<o></o>

 

[gsbelbin]

Hi
It sounds like a job for Conditional Formatting. Select the cells H4 to H7 and G30 and O30 (using CTRL key for multiple selections). Select Format | Conditional Formatting and Formula Is from the drop down. Then enter the following formula and choose the interior colour that you want and click OK.

=$H$7="x"

Cheers

Gordon

 

[trenny]

I was hopiong to do this using a macro. I have 1000 files to work on. Something like:

if cells(h7).value = "x" then offset(0,-1) color = red

 

[trenny]

I was doing it right all along, but it wasn't working before, but now it is???

For anyone else, here is a snippet of the code:

Range("H7").Select
If ActiveCell.Value = "x" Then
ActiveCell.Interior.ColorIndex = 3
ActiveCell.Offset(0, -1).Interior.ColorIndex = 3
ActiveCell.Offset(0, -2).Interior.ColorIndex = 3
ActiveCell.Offset(0, -3).Interior.ColorIndex = 3
ActiveCell.Offset(0, -4).Interior.ColorIndex = 3
End If

I just repeat it for each cell I need to evauluate.