Conditional Formatting

[peterv6]

I am using EXCEL 2008.

I want to conditionally format the cells in column A depending on the values in the corresponding cells of a different columns, say columns B & C.

Column A would have dates.
Columns B & C would have number values representing hours and minutes in decimal form.

A B C
1/1/2022 0.50
1/2/2022 0.75
1/3/2022 1.50
1/4/2022 3.00
1/5/2022
1/6/2022 1.25
1/7/2022

If a cell in column A has a non-blank value in column B, I'd want the value in the cell in column A to be colored Green.
If a cell in column A has a non-blank value in column C, I'd want the value in the cell in column A to be colored Red.

If the values in both columns B & C are blank, I'd leave the formatting in the corresponding cell in column A the default color.

On an individual basis, I can do this using conditional formatting, but is there any way to apply this to an entire column?

I know how to do that with copy & paste for individual cells, but I can't figure out how to do it for an entire column based
on the cells in a corresponding column. If anyone can tell me if this is possible, and give me an idea how to do it, I'd be
eternally grateful. TIA

 

[Peter_SSs]

I do not have such an old version to test with but this should still work.
I have assumed headings in row 1

• Select from A2 down to the end of your data with A2 being the active cell.
• Set up the CF rules as shown below. Excel will automatically adjust the row numbers as it applies the CF down the column.

22 01 29.xlsm
	A	B	C
1	Date	Val 1	Val 2
2	1/1/2022	0.5
3	1/2/2022		0.75
4	1/3/2022	1.5
5	1/4/2022	3
6	1/05/2022
7	1/6/2022		1.25
8	1/07/2022
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A8	Expression	=C2<>""	text	NO
A2:A8	Expression	=B2<>""	text	NO

 

[jtakw]

Hi,

What if Both B & C are non-blank?

 

[peterv6]

Hi,

What if Both B & C are non-blank?

Good question, but the calculations in my spreadsheet ensure that only one of the two columns would have a non-blank value. Thanks for pointing that out.

 

[jtakw]

Good question, but the calculations in my spreadsheet ensure that only one of the two columns would have a non-blank value. Thanks for pointing that out.

Then your question is answered in Post #2 by @Peter_SSs

 

[peterv6]

I do not have such an old version to test with but this should still work.
I have assumed headings in row 1

• Select from A2 down to the end of your data with A2 being the active cell.
• Set up the CF rules as shown below. Excel will automatically adjust the row numbers as it applies the CF down the column.

22 01 29.xlsm
	A	B	C
1	Date	Val 1	Val 2
2	1/1/2022	0.5
3	1/2/2022		0.75
4	1/3/2022	1.5
5	1/4/2022	3
6	1/05/2022
7	1/6/2022		1.25
8	1/07/2022
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A8	Expression	=C2<>""	text	NO
A2:A8	Expression	=B2<>""	text	NO

This looks like a good solution, unfortunately, Excel 2008 doesn't have this capability. The only options it gives is "cell value is" or "formula is".
I'm no longer in it, and it wouldn't be cost effective to upgrade, unfortunately. Thanks anyway.

 

[peterv6]

Then your question is answered in Post #2 by @Peter_SSs

As I replied to Peter, Excel 2008 does not have those conditional formatting options. Unfortunately, it looks like what I want to do can't be done in this version.

 

[Peter_SSs]

Use the "formula is" option

 

[peterv6]

Use the "formula is" option

OUTSTANDING! Thank you so much Peter. Your simple explanations really made it easy to comprehend. Have a great weekend!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.