Conditional Formatting

[treznorse]

I have two sets of data, 14 columns each. The data contained in the colums is a sort of Before and After analysis. I would like to highlight the cells in the After dataset based on certain criteria but I'm not certain how to setup the Formatting rules.

Here is a rundown of the rules I need to use. Keep in mind that the values in the After columns need to refer to the values in the Before columns before the Conditional Formatting is applied.

• Green Highlighting means that before the value was 0-5 and after the value is 6 or more.
• Yellow Highlighting means that before the value was blank and after the value was 1-5.
• Red Highlighting means that before the value was >1 and after the value was 0.
• No Fill means that before the value was either blank, 0, >6 or <=5 and the after values remained the same. Example, if before a value was 50 and after it was 300, then no fill color is needed.

Any help with this issue would be very much appreciated.

 

[Jonmo1]

Welcome to the board...

Let's say that Column F is the AFTER data set, and Column A is the Before...
F2 should compare to A2..
F2 will be the cell highlighted according to the rules..

Green Highlighting means that before the value was 0-5 and after the value is 6 or more.
Yellow Highlighting means that before the value was blank and after the value was 1-5.
Red Highlighting means that before the value was >1 and after the value was 0.

No Fill means (all situations that do not meet any of the previous 3 rules).


Highlight F2
format - conditional formatting

Condition 1:
Formula Is
=AND(A2>=0,A2<=5,F2>=6)
Format for Green

Condition 2:
Formula Is
=AND(LEN(A2)=0,F2>=1,F2<=5)
Format for Yellow

Condition 3:
Formula Is
=AND(A2>1,F2=0)
Format for Red


Hope that helps.

 

[treznorse]

How do I apply this formatting to the whole AFTER dataset? Can I still use the reference between the first data point in each set to apply to whole AFTER set or will the conditional formatting reference the first cell?

 

[Jonmo1]

It depends on what you want...

Given my above example, column A is before, column F is after...

To apply it to the whole set, do you then want B and G, then C and H ?
If yes, then you can just copy the cell F2, and paste special - formats onto G2
The ranges will auto update.
Then you can copy F2 and G2 together, and paste special - formats to F3 and G3

Etc...

 

[treznorse]

Okay, that worked, but where there were blanks in the Before data, the corresponding numbers in the After data were replaced with blanks. How can I retain the values in the After data without fill.

 

[Jonmo1]

Not sure I understand...

I think only the 3rd condition (red) would be effected by blanks in F2...

Perhaps the 3rd condition formula should be

=AND(A2>1,F2=0,F2<>"")

 

[treznorse]

That fixed the blank problem.

Having trouble with the yellow formatted cells. I want it to identify cells in the After data that have values of 1-5, regardless of what the corresponding cells in the Before data have, the one exception to this is it needs to be No Fill when the value in the Before set was 1-5.

Sorry for all the trouble!

 

[Jonmo1]

Glad to help...

Try this for Yellow

=AND(NOT(AND(A2>=0,A2<=5)),F2>=1,F2<=5)

 

[treznorse]

It still isn't catching the cells that went from blanks in the Before columns to 1-5 in the After column.

 

[Jonmo1]

I'm not sure I understand anymore...

Let's clarify a few things...

1. stop calling it before and after...
From here on out, let's say A2 and F2

2. Should Blank be considered a 0 ? or are Blank and 0 seperate from each other?

3. Are your blanks really blank?
To test, put these formulas in some available cells
=ISBLANK(A2)
=LEN(A2)
What do they return when A2 is one of your blanks?

4. Please redefine the criteria...

IF A2 is blank AND F2 is 0-5 Then color F2 ??
If A2 is blank AND F2 is 6 or more Then color F2 ??
If A2 IS 0-5 AND F2 is 0-5 Then color F2 ??
If A2 IS 0-5 AND F2 is 6 or More Then color F2 ??

etc...
Please consider and describe ALL possible combinations of A2 and F2