Range: changing interior color

[Jim Brass]

I have a spreadsheet that has info in columns A thru F. I am trying to write
a macro that will background color the row if the cell in column F is
greater than Zero. In other words if in row 11 cell F11 is 5, I want cells A
thru F of row 11 to be colored green. I have tried

If F11 > 0 then Range("A11:F11").InteriorColor = 4

but it doesn't work. What should I do? By the way, I want this to work for
values in rows 11-22. Thanks Jim

 

[Norman Jones]

Hi Jim,

This can be achieved with conditional formatting.

Select the range to highlight, say A11:Z22
Format | Conditional Formatting
Formula is: =$F11>0
Select a pattern color
OK

 

[Guest]

You can do this without a macro using conditional formatting:

Select all the cells that the fomatting will apply to (hold and drag
starting from the top row).
Click Edit->Conditional Formatting
Change the dropdown from "Cell Value Is" to "Formula Is" and type "=$F11 >
0" in the textbox
Click the format button and make the background green.
Hit OK then OK again and that's it.


Within a macro (if you have to have it):

Sub SetRangeColor()
For i = 11 to 22
if Range("F" & i).Value > 0 Then Range("A" & i & ":F" &
i).Interior.Color = vbGreen
Next
End Sub

 

[Leith Ross]

Hello Jim,

Change your line of code to...

If F11 > 0 then Range("A11", "F11").Interior.Color = 4

Sincerely,
Leith Ros

 

[Ken Puls]

I agree with the other responses that you should do this with conditional
formats.

For reference, you're macro would work if you changed it to:

If Range("F11").Value > 0 then Range("A11:F11").InteriorColor = 4

Cheers,