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
 

Mustafa Evans

New Member
Joined
Mar 18, 2010
Messages
35
Office Version
  1. 2010
Platform
  1. Windows
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. ;)
Sorry, what other issue. I could only see 1 point and the 2nd point just says 2....

Thanks for the help.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Mustafa Evans

New Member
Joined
Mar 18, 2010
Messages
35
Office Version
  1. 2010
Platform
  1. Windows
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. ;)
Ah sorry, missed that !!!.
8006-0a8afbca8e9395367f2a3e5f7b66899f[1].png

Sample Data2.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,516
Office Version
  1. 365
Platform
  1. Windows
Thanks for the samples at last. ;)

See if you can use this. I have shown a few more columns, some of which are hidden in your image and could be hidden with my idea.
This Conditional Formatting has been applied from G3:Q20 but currently, it will only highlight relevant values in column I. As soon as values are entered in column J, any highlights will disappear from column I and appear where relevant in column J, as shown in the second screen shot below.

20 03 02.xlsm
FGHIJKLMNOPQAHAJ
2NovDecJanFebMarAprMayJunJulAugSepOct
3x
4x
536%51%49%59%Up Good
6x
7x
8x
9x
10x
11x
1215%14%0%15%Up Bad
13x
1499%98%99%99%Up Good
15100%99%98%98%x
1639%39%17%17%Up Bad
17109%75%55%75%Down Bad
18109%75%79%55%Down Good
1943%38%28%28%x
2098%97%90%90%Down Bad
CF Red Green
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G3:Q20Expression=AND(RIGHT($AJ3,3)="Bad",ABS(G3-F3)>=10%,COUNTA(G3:$Q3)=1)textNO
G3:Q20Expression=AND(RIGHT($AJ3,4)="Good",ABS(G3-F3)>=10%,COUNTA(G3:$Q3)=1)textNO



20 03 02.xlsm
FGHIJKLMNOPQAHAJ
2NovDecJanFebMarAprMayJunJulAugSepOct
3x
4x
536%51%49%59%60%Up Good
6x
7x
8x
9x
10x
11x
1215%14%0%15%20%Up Bad
13x
1499%98%99%99%80%Up Good
15100%99%98%98%98%x
1639%39%17%17%35%Up Bad
17109%75%55%75%75%Down Bad
18109%75%79%55%40%Down Good
1943%38%28%28%30%x
2098%97%90%90%80%Down Bad
CF Red Green
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G3:Q20Expression=AND(RIGHT($AJ3,3)="Bad",ABS(G3-F3)>=10%,COUNTA(G3:$Q3)=1)textNO
G3:Q20Expression=AND(RIGHT($AJ3,4)="Good",ABS(G3-F3)>=10%,COUNTA(G3:$Q3)=1)textNO
 

Mustafa Evans

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

ADVERTISEMENT

Thank you for your help with this. This is a brilliant solution that I hadn't thought of, especially as it will be a one fix solution for on-going months.

The only thing, although it works very well, it's not quite there, as your formula only looks at good and bad, there are occasions when cells should be GREEN only if the Key reads Up Good, where the percentage change of 10% applies and the %age has increased by 10% on the previous month. Currently it only sees Good as Green, and Bad as RED.

I hope you understand, it's just that I need to get this right for my presentation on Friday.

I have attached a screenshot with comments in AK.

Many thanks again for your help.Sample Data3.png
Sample Data3.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,516
Office Version
  1. 365
Platform
  1. Windows
Perhaps you can see better now why I kept asking for examples before as we didn't have examples like these until now. ;)

I'm hoping your comment in row 10 is wrong. Rows 10 & 11 both say "Down Bad" and both rows go up by 10% yet you say one should be red and one should be white? My current understanding says both should be white.

Try these

20 03 02.xlsm
FGHIJKAJ
1NovDecJanFebMarApr
2x
351%0.61x
461%71%Up Good
520%10%Up Good
610%20%Up Bad
740%30%Up Bad
810%20%Down Good
930%20%Down Good
100%10%Down Bad
1110%20%Down Bad
120%10%Down Good
13x
1498%88%Up Good
1598%x
1617%27%Up Bad
1755%45%Down Bad
1879%89%Down Good
1928%28%x
2090%100%Down Bad
CF Red Green (2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G2:Q20Expression=AND(RIGHT($AJ2,3)="Bad",(G2-F2)*IF(LEFT($AJ2,2)="Up",1,-1)>=10%,COUNTA(G2:$Q2)=1)textNO
G2:Q20Expression=AND(RIGHT($AJ2,4)="Good",(G2-F2)*IF(LEFT($AJ2,2)="Up",1,-1)>=10%,COUNTA(G2:$Q2)=1)textNO
 

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156

ADVERTISEMENT

your formula for red returns 1 for i16 as i16-h16 = 0 which is <=10%. maybe you need to switch up your logic if you want to have that being left white.
 

Mustafa Evans

New Member
Joined
Mar 18, 2010
Messages
35
Office Version
  1. 2010
Platform
  1. Windows
Perhaps you can see better now why I kept asking for examples before as we didn't have examples like these until now. ;)

I'm hoping your comment in row 10 is wrong. Rows 10 & 11 both say "Down Bad" and both rows go up by 10% yet you say one should be red and one should be white? My current understanding says both should be white.

Try these

20 03 02.xlsm
FGHIJKAJ
1NovDecJanFebMarApr
2x
351%0.61x
461%71%Up Good
520%10%Up Good
610%20%Up Bad
740%30%Up Bad
810%20%Down Good
930%20%Down Good
100%10%Down Bad
1110%20%Down Bad
120%10%Down Good
13x
1498%88%Up Good
1598%x
1617%27%Up Bad
1755%45%Down Bad
1879%89%Down Good
1928%28%x
2090%100%Down Bad
CF Red Green (2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G2:Q20Expression=AND(RIGHT($AJ2,3)="Bad",(G2-F2)*IF(LEFT($AJ2,2)="Up",1,-1)>=10%,COUNTA(G2:$Q2)=1)textNO
G2:Q20Expression=AND(RIGHT($AJ2,4)="Good",(G2-F2)*IF(LEFT($AJ2,2)="Up",1,-1)>=10%,COUNTA(G2:$Q2)=1)textNO

Thanks Peter, thank you so much for your patience, it works perfectly, and yes I was at fault with lines 10 & 11 !!.

I usually like working things out myself so that I can understand them, this was a little beyond me.

Regards.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,700
Messages
5,637,887
Members
416,988
Latest member
Ahmed_Yehia

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