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
 

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
Whose formula?

Which I16? There have been several shown throughout the thread.
sorry was replying when you were so my comment is out of order, was referring to the conditional formatting formula.
Definitely need the criteria to be better explained and enough samples to illustrate the desired results. I'll leave you with this one, I think you have it well in hand
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Mustafa Evans

New Member
Joined
Mar 18, 2010
Messages
35
Office Version
  1. 2010
Platform
  1. Windows
My
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

Further to the solution you gave, I now am told I need to improve it !!

The criteria in the key :

Up Good
Up Bad
Down Good
Down Bad

are subjective, as if the 1st criteria is met of being =>10%, and the key reads for example "Up Good", if in fact the result went down by 10% then the opposite is true and the cell should turn red.

Please remember that each key relates uniquely to each reporting statement, but if the key reads one way, then the opposite is also true and the cell should turn the opposite colour.

I hope my ramblings make sense.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,444
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I hope my ramblings make sense.
I'm not sure. You should be understanding by now that a good variety of examples helps clarify. Are these the correct colours for this data. For any that are incorrect please give a detailed explanation of what colour they should be and why.

Mustafa Evans 2020-03-06 1.xlsm
HIJKAJ
1JanFebMarApr
2x
351%0.61x
461%71%Up Good
520%10%Up Good
610%20%Up Bad
740%38%Up Bad
810%20%Down Good
930%20%Down Good
100%5%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%85%Down Good
1928%28%x
2090%100%Down Bad
CF Red Green (3)
 

Mustafa Evans

New Member
Joined
Mar 18, 2010
Messages
35
Office Version
  1. 2010
Platform
  1. Windows
I'm not sure. You should be understanding by now that a good variety of examples helps clarify. Are these the correct colours for this data. For any that are incorrect please give a detailed explanation of what colour they should be and why.

Mustafa Evans 2020-03-06 1.xlsm
HIJKAJ
1JanFebMarApr
2x
351%0.61x
461%71%Up Good
520%10%Up Good
610%20%Up Bad
740%38%Up Bad
810%20%Down Good
930%20%Down Good
100%5%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%85%Down Good
1928%28%x
2090%100%Down Bad
CF Red Green (3)

The colours in the data as 100% correct, so sorry for the trouble.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,444
Office Version
  1. 365
Platform
  1. Windows
The colours in the data as 100% correct,
Hopefully you are wrong about that as row 14 appears to be the same as row 5 so should be red too?

Mustafa Evans 2020-03-06 1.xlsm
FGHIJKAJ
1NovDecJanFebMarApr
2x
351%0.61x
461%71%Up Good
520%10%Up Good
610%20%Up Bad
740%38%Up Bad
810%20%Down Good
930%20%Down Good
100%5%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%85%Down Good
1928%28%x
2090%100%Down Bad
CF Red Green (3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G2:Q20Expression=AND(OR(AND(RIGHT($AJ2,3)="Bad",(G2-F2)*IF(LEFT($AJ2,2)="Up",1,-1)>=10%),AND(RIGHT($AJ2,4)="Good",(G2-F2)*IF(LEFT($AJ2,2)="Up",-1,1)>=10%)),COUNTA(G2:$Q2)=1)textNO
G2:Q20Expression=AND(OR(AND(RIGHT($AJ2,4)="Good",(G2-F2)*IF(LEFT($AJ2,2)="Up",1,-1)>=10%),AND(RIGHT($AJ2,3)="Bad",(G2-F2)*IF(LEFT($AJ2,2)="Up",-1,1)>=10%)),COUNTA(G2:$Q2)=1)textNO
 

Watch MrExcel Video

Forum statistics

Threads
1,129,260
Messages
5,635,131
Members
416,842
Latest member
Ateen4ever

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