Conditional Formatting Help

[archie8]

I have a list of data that I am trying to format. The length changes depending on what I'm doing. If in one of the rows, a certain 2 cells are empty, then I want to highlight a range of cells in that row. If either of these cells has data in it (a date), then I would like to highlight it a different color. Since the number of rows changes, I don't want this to start highlighting rows after my data ends. Any tips on how to do this? I assume I use a formula through conditional formatting but this is my first attempt at something like this and I'm a little lost.

[ChemistB]

Assuming date in B starting at B2, Other cell is column D and that when you have data, column A is always filled in. If you want E:H conditionally formatted, select E:H
Conditional Formatting>New Rule> Use Formula

=AND($A1<>"", $B1="", $D1="") format as desired
=AND($A1<>"", ISNUMBER($B1), $D1="") format differently
Does that work for you?

[Special-K]

Maybe something like this (untested)

Assume the "certain 2 cells" are A1 and B1
Assume also that "cells that have data" are C1 to E1

Conditional Formatting

For both cells empty
Select C1
New Rule
Use a formula to determine...
with the formula
=(AND(COUNTA($C1:$E1) < > 0,COUNTA($A1:$B1)=0))
and format as required.

Use Format painter to copy down the column

For one or more cells not empty
Repeat with this new formula
=(AND(COUNTA($C1:$E1) < > 0,COUNTA($A1:$B1) < > 0))

[archie8]

Assuming date in B starting at B2, Other cell is column D and that when you have data, column A is always filled in. If you want E:H conditionally formatted, select E:H
Conditional Formatting>New Rule> Use Formula

=AND($A1<>"", $B1="", $D1="") format as desired
=AND($A1<>"", ISNUMBER($B1), $D1="") format differently
Does that work for you?

I'm trying this one first, haven't looked at the second yet. I did get that to work but I have 2 questions.

First, doing it this way I think I end up having 4 formulas. Using your columns, one for if B is filled and D is not, one for if D is filled and B is not, one for if both are filled, and one for if neither are filled. Couldn't you have one condition for if they were both empty (say format red), and have another condition as a 'if the first condition isn't true', format green.

Second, I can get this to work just fine for one row. What happens if I want to reproduce this for 10 rows or 30 rows?

[ChemistB]

I don't think you will need additional formulas. Upload a spreadsheet with an example of the different possibilities and what you would expect to see (Go Advanced> Manage Attachments)

If you select the entire range that you wish to conditionally format (in my example it was all of E,D,F,G and H) and then set up the formulas as you would for the first cell in your range (i.e. E1), which is what I did, then Excel will configure each cell appropriately. No need to copy or modify anything.

[archie8]

I've attached a sample.

[ChemistB]

Okay, since you already have the conditions in place, we'll work from there
Conditional Formatting>Manage Rules
Change the "Applies to" field to cover your full range (i.e. =$C$2:$F$500) for both conditions

You were right about the second formula not being enough (the green one). Select it and edit
=AND($A2<>"",OR( AND(ISNUMBER($C2), $D2=""), AND($C2="", ISNUMBER($D2))))
That will turn green if either C2 or D2 (but not both) are empty.
See attachment

[archie8]

That works great. Thanks for your help.