Need code to change cell colors 1

[westma]

Greeting,

I have data in cells A57:F65536 and I need the cells in the row to turn yellow if the cell in column B corresponding to that row contains a zero. In other words, if B100=0, then cells A100:F100 would turn yellow, I can't figure out the code and I was wondering if someone could help me out.

Thanks,
matt

 

[Zathras]

You don't need code.

Use conditional formatting:

1. Select cell A1
2. Type {Control}+A to select all cells
3. Pick Format/Conditional Formatting... from the menu
4. Change "Condition 1" to "Formula Is"
5. Enter the formula:[blue]

      =AND($B1=0,ROW($B1)>=57)

[/color]
6. Click "Format..."
7. Select "Patterns"
8. Click on your color.
9. Click OK
10. Click OK

 

[JohnCHolmes]

Well done - star.

 

[westma]

Cool! Thanks a bunch

best regards,
matt

 

[dan1967]

You might be able to do this easier with Conditional Formatting. Highlight your cells in (Ax:Fx) and go to Format->Conditional Formatting and set your parameters and formats.

You will want to set 2 conditions:

1) Formula is: "=ISBLANK(B1)=TRUE" and set your format;
2) Formula is: "=B57=0" and set your format;

The first condition handles any empty cells in column B.

Dan.

 

[JohnCHolmes]

Regarding Zathras solution: It might be preferable to eliminate the ROW clause and instead just apply the conditional formatting to only rows 57+. The potential drawback otherwise is if you e.g. delete rows 1-50, the condition will still be married to 57+, and if you then wanted the coloring for 7+, you wouldn't get it.

But it all depends on your scenario. Maybe you want the 57 to be hard.