[moff]

Hello and thanks for reading this.
Simple question regarding conditional formatting, but for some hours and much googling didn't find the answer:
I want a cell, let's say A1, to be color pink if D1 is empty, but blue if D1 is not.
Then, how do I copy the rule(s) to the range A2:A58 and/or C1:C58).
Any help will be greatly appreciated (specially a useful one)...
moff

[wmckelvey]

moff, on 13 January 2012 - 10:22 AM, said:

Hello and thanks for reading this.
Simple question regarding conditional formatting, but for some hours and much googling didn't find the answer:
I want a cell, let's say A1, to be color pink if D1 is empty, but blue if D1 is not.
Then, how do I copy the rule(s) to the range A2:A58 and/or C1:C58).
Any help will be greatly appreciated (specially a useful one)...
moff

I was curious and did some trial and error. You need to set this up using the format option. I tried to post a picture but can't, so you will have to follow below.
Select the cell you want to change color.
On the menu bar select "Format"
At the bottom of the drop down menu select "Conditional Formatting"
You will now have a box with one condition. You will need to set it up with the following:
In the condition drop down select "Formula Is"
The field to the right will now except an equation. In this box place the following equation =(D1="")
On the right click on "Format"
There are three buttons on the top of the new box select "Patterns". Select your color and click "OK"
This sets the condition for empty.

Now click on the button "Add"
You now have a second condition.
In the condition drop down select "Formula Is"
The field to the right will now except an equation. In this box place the following equation =(D1<>"")
On the right click on "Format"
There are three buttons on the top of the new box select "Patterns". Select your color and click "OK"
This sets the condition for not empty.

Now click "OK". This sets the condition for the cell you selected. To do this to a range of cells, select the range and follow the above directions.

[Martian]

Aslo consider:

• as you copy a formula across to the right or left, a $ symbol preceding the column letter makes the reference ABSOLUTE, otherwise it is RELATIVE
• as you copy a formula down or up, a $ symbol preceding the row number makes the reference ABSOLUTE, otherwise it is RELATIVE

If the condition of A1 thru D1 was dependent on B1 contents; and A2 thru D2 was dependent on B2 contents; etc
you would put this in the A1 conditional formula: =$B1=your criterion;
then copy it across and down. The "$" before the "B" would keep B references absolute, but the row numbers would be relative.

This post has been edited by Martian: 13 January 2012 - 05:30 PM

[moff]

Thank you wmckelvey and Martian.
Before posting I was doing exactly what you said, wmckelvey, but what I was missing is that I tried to use in A1 a "function oriented formula" ["=IF(D1="")] and [=IF(D1<>"")] without knowing that the "grammar" is different in the conditional formatting formula.
Great! It works and that's all I needed!
Very grateful that you took your time!

Martian, thank you for your clarification!
Have both a great coming week.
moff

[EGM]

Another way, which I find simpler, is to select the entire area that you'll want to format. (Drag to select a single contiguous region; use the Command key to add non-contiguous regions.) Then apply conditional formatting to whatever cell happens to end up selected (white with blue outline) when you've finished selecting all of them. The same conditional formatting will be applied to all the cells you selected.

Example:

Fill cells b3:b8 with the number "3."
Fill cells a3:a8 with alternating 2-4-2-4-2-4.
Select a3:a8 by dragging from a3 to a8. Cell a3 will be highlighted.
Select Conditional Formatting>Highlight Cells Rules>Greater Than.
Enter "=b3" (without the quotation marks; note that this is a relative reference) in the box to the right of the words "greater than." Leave the default pink formatting.
Click OK.
The cells will be alternating white-pink-white-pink-white-pink.

This post has been edited by EGM: 16 January 2012 - 04:19 PM