Microsoft Excel - Conditional formatting - Asked By david on 22-Sep-12 02:56 AM


Would it possible to create a formula to apply a particular fill colour to a number of cells in a row based on one cell in the same row resulting in specific criteria?

E.g. I have several columns B:AV. I have also up to 1000 rows within this range starting from row 9. Various cells in each row are formulated to meet criteria based on input data from other cells within the same row.

I have also used column BA which is formulated to return data in the same row based on other data being selected in the same row.

All the above works fine.

In would like cells in B9:AV:9 to change the fill colour to a pre selected colour (rose) only when BA9 cell returns a zero or blank value.

I would like to then copy this into the other rows (up to 1000 rows)

Any help is appreciated.


Harry Boughen replied to david on 22-Sep-12 04:39 AM
Hi David,

Select  the range of cells that you want to be formatted.  Select Format, Conditional Formatting, Formula Is.  In the formula box enter $BA$9 = 0 and set your fill colour to the required colour  using the Format Button, Patterns Tab.  Click OK and Bob should be your uncle or at least your aunt's live-in lover.


Harry Boughen replied to david on 22-Sep-12 04:50 AM
Sorry David,

I may have misread your post.

Perhaps this is what you require.

Select B9:AV9. Then Format, Conditional Formatting, Formula Is and enter $BA9 = 0 as the formula.  Select your formatting as outlined in my last post.

Then with B9:AV9 still selected, click the format painter.  You will get the crawling ants around B9:AV9.  Then select B10:AV1009 and you should be done.


Jitendra Faye replied to david on 24-Sep-12 12:49 AM

Conditional formatting enables you to highlight cells with a certain color, depending on the cell’s value.

For example,
highlighting positive values with green background color, and negative values with red.
(actually not only the color can be set, but every aspect of cell formatting can be applied this way - font style, size, bold, underline etc.)

Follow these links-

Hope this will help you.


david replied to Jitendra Faye on 19-Oct-12 01:20 PM
thank you all for the help all works well now