Fill cell colour based on another cells contents.

To follow what you have, in the name box (above the row numbers and left of the column letters) enter C18:AK22. That will select all the rows and columns between C18 and AK22.

Go to Conditional formatting, New Rule, Use Formula..., and enter this and then go to the formatting and choose the formatting options that you want.

Formula:

=C18:AK22>0

All columns between C and AK and the rows 18 and 22 will now have the conditional formatting.

For Quantity 1, select C18:AK18 (Your original columns msg#1) Enter this formula for the first quantity and format as desired. Any entry will be conditionally formatted.

Formula:

=C18<>""

For Quantity 2 (row 20) Select C20:AK20 as above.

Formula:

=C20<>""

For the Item row Select C24:AK24 and enter this formula in Conditional Formatting

Formula:

=OR(C18>0,C20>0)

I fail to see how the calendar works. It seems to have little in common with the original question as far as I can see.

If that is the way that you are doing data entry, that looks like a lot of work for little benefit especially if you have to do that for every month of the year, every year.

What is this supposed to accomplish? Can you elaborate?

Maybe this is closer to what you want. Values >0 are highlighted and text values are not highlighted except in the bottom row of each section.

I have no idea if this formatting will be over-written when populated with new data. If "values only" are imported, I would be more confident.

First: select the area E31:AM38 and create the rule

Formula:

=AND(E31<>"",ISNUMBER(E31))

Second: select E40:AM40 and create the rule

Formula:

=SUM(E31:E38)<>0

Third: Select E31:AM40 and copy.
Select E42 and the Paste Special Formats. Repeat this for each block down the worksheet.

You are doing something wrong or something differs from your sample. There are 0 values in the ranges hidden by a format GENERAL;; and those will be formatted because 0 even though the 0 isn't visible. This not the same as an empty or even a null cell.

Upload your real file if you can. This file shows the 0 values in cells that were hidden by formatting and are therefore being shown as empty when they are in fact not empty. There are also many cells with an apostrophe and there is no apparent reason for this.

Glad you got it working. If those 0 values are important, you can change the formatting to GENERAL. If you don't want to see them, do nothing.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.