Row Banding + column hi-lite

[Attila]

I can do one or the other but not both? Using =MOD(ROW(),2) I get the row banding for easier reading but when I add in a conditional format for a column all the cells in the column are shaded instead of every second one. What I am trying to end up with is every second row shaded and any entry in the one column that exceeds a specified value to be displayed in red font. How can I get both conditions to occur without interfering with each other?

[shg]

The first conditional format meeting the criteria is applied and others are ignored, so here you need three conditional formats:

One for red font and shading (this must be first)

One for shading only

One for red font only

[Andy Pope]

You need to define 3 conditions.
For a range starting in D1 where red font is applied to cells with a value greater than 7.

Formula - format Red font and row shade colour
=AND(MOD(ROW(),2)=0,D1>7)

Formula - row shade colour
=MOD(ROW(),2)=0

Cell Value Greater than 7 - red font

[NBVC]

Another CF combination that should work....

It assumes column D must be greater than 7 is your other condition

see attached

[Attila]

I'm not getting something? The first 2 suggestions didn't work at all, several columns and rows were affected? The third suggestion gave an error, something about = and - in the sane formula? I attached a screen shot. I would like rows 6 - 299 banded and column V (6 - 299) should go red when equal to or greater than 10:00.

[arthurbr]

Hi Attila,
if you could post a small sample of your data as an xls file ( see "Manage attachments" in the Reply to thread form), we will be able to help you much better
Cheers

[shg]

Why post a picture if you want help with a spreadsheet?

[NBVC]

Judging by the image... I think you need to wrap the 10:00 in quotes and add +0 immediately after and before the closing parenthesis

[Attila]

shg - because I'm not to bright? lol
Workbook is far to big to post so i thought a screenshot would help, I guess not? I cut out a bunch of stuff to get the size down. I have the banding in place and I would like to have it so any value in col V the is equal to or greater than 10:00 shows in red font. On the worksheet V8 & V11 should show up in red font without screwing up the banding?

[shg]

You could post an ad-hoc workbook and someone will plug in the CF.

[Attila]

is my condensed workbook screwed up and can't be opened?

[shg]

is my condensed workbook screwed up and can't be opened?

No; I just never saw it. Editing your post does not bring it to the top of the queue.

See attached.

I use V6 to contain the value that triggers the red font, but you can put it elsewhere or hard-code it the formulas.

[Attila]

Thanks shg I didn't notice your last post, we must have put them up about the same time? I tried you clip but it turns all text red? I think I typed it in exactly as you showed it, some of it is unclear (looks like something before the M in the first line but I'm not sure what). I will look at it again tomorrow as it's time for work.

[shg]

Take a closer look. In the example posted, you can see examples of the default formatting and all three conditional formats.

[Attila]

What am I missing? I tried several times and always the same result, the banding works fine but all cells have red font? I only want entries = to or > than 10:00 in column V to display as read? Attached is what I coded in my workbook, I'm not seeing what is differant from the example posted in message 12?

[ChemistB]

This might help. I applied shg's conditional formatting to your workbook. If you want to have the value directly in the conditional formulas you need to put it in quotes and add 0, eg

Please Login or Register  to view this content.

See attached.

ChemistB
My 2¢