Conditional Formatting based upon value of another cell

[theuniquenerd]

I'll try and explain my issue and what I'm looking for as best I can. If I'm not clear, please let me know.

I'm trying to figure out how to apply conditional formatting to almost "remove" the formatting if a certain condition applies.

I have a conditional formats for:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Rule (applied in order shown)
[/TD]
[TD]Format
[/TD]
[TD]Applies to
[/TD]
[/TR]
[TR]
[TD]Cell value <=$A$1
[/TD]
[TD]Light Red Fill with Dark Red Text
[/TD]
[TD]$C$6:$D$9
[/TD]
[/TR]
[TR]
[TD]Cell value between $A$2 and $B$2
[/TD]
[TD]Light Red Fill with Dark Red Text
[/TD]
[TD]$C$6:$D$9
[/TD]
[/TR]
[TR]
[TD]Cell value between $A$3 and $B$3
[/TD]
[TD]Yellow Fill with Dark Yellow Text
[/TD]
[TD]$C$6:$D$9
[/TD]
[/TR]
[TR]
[TD]Cell value between $A$4 and $B$4
[/TD]
[TD]Green Fill with Dark Green Text
[/TD]
[TD]$C$6:$D$9
[/TD]
[/TR]
[TR]
[TD]Cell value > 99
[/TD]
[TD]Green Fill with Dark Green Text
[/TD]
[TD]$E$6:$E$9
[/TD]
[/TR]
</tbody>[/TABLE]





I'm trying to apply a conditional format rule that will allow the formatting to be removed only from the respective dates in columns C & D if a cell in the same row in column E is 100, ie, it's complete and the row doesn't need to be highlighted for attention purposes.




Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]4/1/17
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]4/2/17
[/TD]
[TD]5/1/17
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]5/2/17
[/TD]
[TD]9/30/17
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]10/1/17
[/TD]
[TD]4/1/18
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]START
[/TD]
[TD]COMPLT
[/TD]
[TD]PCT COMPLT
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD]Grant Write
[/TD]
[TD]9/1/16
[/TD]
[TD]4/1/17
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD]Grant Review
[/TD]
[TD]10/1/16
[/TD]
[TD]5/1/17
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD]Grant Final
[/TD]
[TD]11/1/16
[/TD]
[TD]6/1/17
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD]Grant Submit
[/TD]
[TD]12/1/16
[/TD]
[TD]7/1/17
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]



Any help would be greatly appreciated!!

 

[James_Latimer]

You'd need to use a formula for your conditional formatting... something like

=$E6 >= 100

Just apply that CF to cell C6:D6 and then you can use 'format painter' to apply it to the rest of the cells in that column

You will probably need to have this CF at the top of your list too as youd want this formatting to take precedence over the others... i hope this makes sense!

 

[theuniquenerd]

You'd need to use a formula for your conditional formatting... something like

=$E6 >= 100

Just apply that CF to cell C6:D6 and then you can use 'format painter' to apply it to the rest of the cells in that column

You will probably need to have this CF at the top of your list too as youd want this formatting to take precedence over the others... i hope this makes sense!

Thank you for your reply!!

It did it, except that when I try to format painter it down, the $'s in the CF carry, therefore the CF doesn't apply to the other cells.

Any ideas how to get rid of the auto $'s?

Thanks so much for your help!

 

[James_Latimer]

Not a problem mate.
Did you make sure that only the column had the $ in front of it. The row number shouldn’t...

 

[theuniquenerd]

Not a problem mate.
Did you make sure that only the column had the $ in front of it. The row number shouldn’t...

Yes I did!

the CF rules manager automatically adds in $'s and there's no way to delete them out, they get added when I hit apply.

If it helps, I'm using Excel 2010

Thanks for the help.

 

[theuniquenerd]

I figured it out with a lot of help from James!

In order to get the format painter to work, you have to format paint using the cells that are listed in the "applies to" field in the rules manager.

Also, the formula rule, even though it lists only the original cell to refer to the changes, when painting it automatically changes the ref cell based on the row that particular format is in.

Also, make sure to put this rule on the top of the list!

This one was a doozy to figure out, and took a better part of 3 days of research to figure out how to do it!

 

[Joe4]

I have never had much luck with the format painter. It never really seems to work the way I expect.
I find it much easier to do apply CF to a multi-cell range in the following manner:
- Select the entire range you want to apply your CF condition to
- Write your CF formula as it pertains to the very FIRST cell in your selected range (Excel will automatically adjust it for the other cells)
The important thing here is to use absolute references in the correct places. Any row or column reference you want locked down, place a "$" in front of it. The references without it will be allowed to float (just like when you autofill a formula).

That is how I always do it, and it hasn't failed me yet!

 

[James_Latimer]

Glad you got it working mate!!