Conditionally remove conditional formatting icons? VBA?

[Tarver]

I have columns of ones (for True) that I format with an icon set. I want to see the green dot, not a one. This works well.

But I also have a Total line, and that total line isn't static. When a list of data ends, a formula adds "Total" in the next empty cell below. It's at that point that I want to total up my green dots and present them as a number.

However, I can't get conditional formatting to switch and turn off the icon at that point.

I've tried adding a new condition that covers that range that doesn't include icons. The icons prevail. I've tried changing the order of the conditions in the conditional formatting dialog box, but the icons remain. I've tried to change the values when an icon appears (so that >2 has no icon) but the cell remains blank in this case.

Is there a way to change the format back to the default when the line reads "Total" in column A?

 

[steve the fish]

All conditional formatting has an 'applied to' range. Just change that to suit.

 

[Fluff]

Something like
=AND($A2<>"Total",$B2=1)

 

[Tarver]

All conditional formatting has an 'applied to' range. Just change that to suit.

That's the issue though. My range of data may be between 300 and 500 lines. A formula sees the end of the range of data and puts "Total" in column A. If I have to change the range of my conditional formatting, then I have to manually do that for every version of this report, for each person that gets a version. That defeats the purpose of automating the report.

 

[Tarver]

Something like
=AND($A2<>"Total",$B2=1)

I'd think that something like this would work, but in the icon set conditional formatting, there's not an option to enter a formula. Icon sets are displayed based upon a cell value.

So I thought I'd create a new condition to override the icon set with something like you suggest, but it doesn't replace the icon set. It only sets a format for that cell that's already filled with an icon.

I'm thinking that VBA may be the way to scan the column for a Total entry in column A and then eliminate the conditional formatting from that row down, but I'm not a great VBA expert and don't have the time to try to code this this week only to discover it can't be done. I was hoping someone could tell me if VBA would work in this situation, or if there were some other way to make a Conditional Format in icon sets conditional on some other value.

 

[steve the fish]

You can have a rule above the icon set and 'stop if true' checked.

 

[Fluff]

Rather than use Icon sets you can use a formula & set a custom format to show a ● which you can then colour by changing the font colour

 

[steve the fish]

eg

=A2="total"

applied to B2:B1000. Check stop if true. Then your icon set below that applied to same range B2:B1000.

 

[Tarver]

The suggestion to use "Stop if true" is brilliant! Thank you!

In all my years of Excel, I've never used this. I have to move my icon sets to the bottom row of my conditions, and then put my "=IF($A4="Total",TRUE,FALSE) on the line above it, but when I checked the "Stop if true" box, it worked like a charm.

Thank you all!