Conditional Formatting issues

Bobjg

New Member
Joined
Dec 11, 2019
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am having issues with conditional formatting. I have setup a few rules that work but others don't? See images of worksheet and rules below. I Just want column H to return formatting based on this IF statement: =IF(G12<=0, "Expired", IF(G12<=60, "Red Flag", IF(G12>90, "Green Flag ", ""))). Pretty straight forward. I have changed the cell formatting to text, general etc.

Any assistance would be great.
1576080456653.png


1576080476107.png


Any assistance would be most appreciated

Thanks - Bob
 

Attachments

  • 1576079812018.png
    1576079812018.png
    102.5 KB · Views: 4

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hi & welcome to MrExcel.
Bit of a guess as you have not shown your CF formula, or said what's wrong.
Try changing your col H formula from
IF(G12>90, "Green Flag ", "")))
to
IF(G12>90, "Green Flag", "")))

ie, remove the space after Green Flag
 
Upvote 0
Fluff,

Thanks for the quick response. Are you able to see the 2 copied images and not just the attached?? The image of the CF Rule manager shows the 3 rules and the Excel wrkst shows only 1 working ?
 
Upvote 0
Yes I can see the images, but they don't show what the rules are.
Did you try what I suggested?
 
Upvote 0
Yes, unfortunately no change the rules read like this: you can see the one that works. I also have a need to expand the current IF statement to include a date value "between" 30-90 days. Can this be include in this statement somehow or?

1576083103020.png
1576083153554.png
1576083177740.png
 
Upvote 0
This formula
=IF(G12<=0, "Expired", IF(G12<=60, "Red Flag", IF(G12>90, "Green Flag ", "")))

Does not put "Soon" into col H, which is why that rule won't work and it has a space after Green Flag, which your CF rule doesn't have, which is why that doesn't work
 
Upvote 0
I have 3 different worksheets I have been working on and have been changing the rules all over the place, needless to say that did it, thanks so much! See below for next issue - should I start new thread?
New Formula: =IF(G12<=0, "Expired", IF(G12<=60, "60-", IF(G12>90, "90+", "")))

Need to identify how to color code between 60 and 90 days in this statement or do I need to figure out how to use "INDEX"?

1576093986327.png
 
Upvote 0
At the moment you are not putting a value in the cell if G12 is between 60 & 90 days.
Why not put something like "60-90" in the cell & then you can check for that.
 
Upvote 0
My apologies if I didn't explain it clearly, any value in G that falls between 60-90 shows nothing in the cell (See rows 53-55). I would like to color code that as well but the current formula leaves that open?
=IF(G53<=0, "Expired", IF(G53<=60, "60-", IF(G53>=90, "90+", ""))). Not sure if I need to add another "IF" piece or something else so it catches those values?
1576161721202.png
 
Upvote 0
the current formula leaves that open
Yes, I know that's why I suggested putting a value in the cell, rather than leaving it blank.
Is that an option?
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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
Back
Top