Conditional Formatting based on key

Mustafa Evans

New Member
Joined
Mar 18, 2010
Messages
35
Office Version
  1. 2010
Platform
  1. Windows
I'm having some problems with using Conditional Formatting in Excel. I have a data set with percentages in monthly columns. Each row is a reporting statement. I need to compare the current month to the previous one and if the change is 10% or more then the current month's cell should change colour.

I have a key in column AJ that states whether the change is good or bad. These include...

UP GOOD
DOWN GOOD
UP BAD
DOWN BAD

I'm always looking for a Significant change of 10% or more.

A good change should be shown in GREEN and a bad change in RED.

If either condition is not met then the cell should remain white.

Can anyone help please.

Mustafa
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
843
Office Version
  1. 365
Platform
  1. Windows
You need two conditional formatting rules. Apply each rule to the column you want to change color. This assumes your data starts in row 2; if not, change the 2.

=RIGHT($AJ2,4)="GOOD"
set format to green

=RIGHT($AJ2,3)="BAD"
set format to red
 

Mustafa Evans

New Member
Joined
Mar 18, 2010
Messages
35
Office Version
  1. 2010
Platform
  1. Windows
Thanks for the reply however both conditions are required for the cell to change colour.

There has to have been a 10% increase or decrease from the previous month and the key needs to correspond with the condition of the change being good or bad.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,517
Office Version
  1. 365
Platform
  1. Windows
Can you show us a small set of sample dummy data and explain the expected results in relation to that sample? XL2BB

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.
 

Mustafa Evans

New Member
Joined
Mar 18, 2010
Messages
35
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Hi, I couldn't use the XL2BB on my work PC. However, I have update preferences and have attached some screenshots of data.

The formula for GREEN is

=IF(AND(OR(AJ3="UP GOOD",AJ3="DOWN GOOD"),I3-H3>=10%),1,0)

and the formula for RED is

=IF(AND(OR(AJ3="UP BAD",AJ3="DOWN BAD"),I3-H3<=10%),1,0)

Cell I5 seems to work but when copied down it doesn't any longer, see I16 for example this should be white and not red.

Hope you can help.

Sample Data.pngSample Data1.png
Sample Data.png
Sample Data1.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,517
Office Version
  1. 365
Platform
  1. Windows
The formula for GREEN is

=IF(AND(OR(AJ3="UP GOOD",AJ3="DOWN GOOD"),I3-H3>=10%),1,0)
That seems to be indicating that "UP GOOD" and "DOWN GOOD are exactly the same. Is that so?

Could we have example rows that demonstrate all 4 of:
UP GOOD
DOWN GOOD
UP BAD
DOWN BAD

Also, are you really wanting this CF to apply to whichever of columns G or H or I or J or ... is the last one with data? If so, what is the last 'Month' column in your hidden section?
 

Mustafa Evans

New Member
Joined
Mar 18, 2010
Messages
35
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

That seems to be indicating that "UP GOOD" and "DOWN GOOD are exactly the same. Is that so?

Could we have example rows that demonstrate all 4 of:
UP GOOD
DOWN GOOD
UP BAD
DOWN BAD

Also, are you really wanting this CF to apply to whichever of columns G or H or I or J or ... is the last one with data? If so, what is the last 'Month' column in your hidden section?

I was trying to use the AND OR IF functions to reduce CF formulas. Basically, we report on the previous month and compare to the previous month, so in this instance we will be reporting on February and comparing with January, there is nothing in the hidden columns, it's just I have put the Key in column AJ in all workbooks.

Each individual reporting statement might increase or decrease monthly and going up or down might be good or bad, hence being green or red.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,517
Office Version
  1. 365
Platform
  1. Windows
1. You are going to write and apply a new CF formula every month?

2. ...
Could we have example rows that demonstrate all 4 of:
UP GOOD
DOWN GOOD
UP BAD
DOWN BAD
 

Mustafa Evans

New Member
Joined
Mar 18, 2010
Messages
35
Office Version
  1. 2010
Platform
  1. Windows
You are going to write and apply a new CF formula every month?

Yes, well to be honest will only be showing the last 3 month months as in the example and hiding everything else, so for this month will be showing Dec, Jan and Feb.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,517
Office Version
  1. 365
Platform
  1. Windows
Yes, well to be honest will only be showing the last 3 month months as in the example and hiding everything else, so for this month will be showing Dec, Jan and Feb.
OK, well I think a single CF formula (for each colour) should 'probably' be able to cope with all months. But I would still like my other issue addressed before offering a suggestion. ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,701
Messages
5,637,893
Members
416,989
Latest member
ash_826

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top