Sleeper: Conditional formats to make things easier on the eye

[Sir Babydum GBE]

Hi

I would like conditional formatting which colours every other different group yellow so that each group stands out.

The group in question is decided in column C, so as long as, say, C4 reads the same as C3, the background will be the same colour as the row above etc.

Now, I managed to do this conditional formatting by creating formulae in column A that returned either a 1, 2, or empty string depending on what was going on in column C, then the conditional formatting coloured the row yellow if the result of the formula in that row was 1.

(don't get what I mean? - here's the formula in column A):

=IF(ISBLANK(C3),"",IF(C3=C2,A2,IF(AND(C3<>C2,A2=1),2,IF(AND(C3<>C2,A2=2),1,""))))

Now I think I've overcomplicated things in this explanation here because what I actually want is conditional formatting to colour every second group yellow - even when an AutoFilter is switched on. So let's say I have groups 1, 2, and 3. Group 1 background is yellow, 2 is white, 3 is yellow and so on. But if a filter completely hides group 2, then I need group 1 to be yellow and group 3 to be white - so that it is obvious I'm looking at two groups.

OK, so I'm crap at explaining - can you help though?

[Aaron Blood]

You're gonna have conisderable trouble making a conditional format recognize filtered results. At least if you want to alternate colors.

However, there is another option. If you look at the seperation example in my conditional formatting example file, you can use that simple conditional format logic with filtered data and I don't think there would be any need for your extra formula in that case.

http://www.xl-logic.com/xl_files/for...ond_format.zip



If you're insistent on a color banding solution, I strongly doubt you'll be able to accomplish that without VBA intervention. In fact, if someone does come up with a condtional formatting solution I'll be glad to post their work on the wall of fame on my website.

[BlueCactus]

You're gonna have conisderable trouble making a conditional format recognize filtered results. At least if you want to alternate colors.

Hehe! Not to mention that conditional formatting seems to be one of the most unreliable of Excel features....

[Sir Babydum GBE]

Thanks for your input. The separation example is certainly useful.

is there a way to creat a custom formula? Such as "If the visible cell above me says x, then say y"?

[Cyberdude]

I would like to tell a short (true) story in order to inject a word of caution into this topic. Years ago, my colleagues decided to jazz up the typically black and white company reports with a little color. So they worked hard to spruce up the next one that was going out. It was a work of art. Everyone liked it . . . everyone except the division head. It seems the gentleman was color blind! He issued a mandate that henceforth color would not be used to "spruce up" the reports.
All I'm trying to say is KNOW YOUR AUDIENCE. I have since found that there are a surprising number of color blind folks out there.

[MWE]

I would like to tell a short (true) story in order to inject a word of caution into this topic. Years ago, my colleagues decided to jazz up the typically black and white company reports with a little color. So they worked hard to spruce up the next one that was going out. It was a work of art. Everyone liked it . . . everyone except the division head. It seems the gentleman was color blind! He issued a mandate that henceforth color would not be used to "spruce up" the reports.
All I'm trying to say is KNOW YOUR AUDIENCE. I have since found that there are a surprising number of color blind folks out there.

Reinforcing the idea of using color cautiously ...

Changing a cell's background color can have quite the opposite effect you intend if you do not also consider the font color. This is particularly true if the file is sent to a b&w printer (b&w laser printers are still a lot better and cheaper than virtually any other device).

Also, I have found that different people and different cultures react to colors differently ... another reason to KNOW YOUR AUDIENCE

[Sir Babydum GBE]

Thanks guys

I'll be using white and yellow backgrounds - the standard colour difference in our department. On b&w printed background it shows as slightly highlighted.

The data sheet the guys will be using will be huge, with not many blank spaces, and we feel that it would be more confusing and harder to navigate if there weren't colour differences to highlight small groups of data.

Of course, I could go the route of many and simply divide the data with blank rows or different sheets. But the data being on one sheet in this fashion facilitates much better reporting and search tools.

So, if anyone knows a vba answer to my question, I'd be very grateful.