I want to conditionally format a cell if two other cells return no value

Tristram_ZX81

New Member
Joined
Jun 22, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
In below picture, C column is sum of A:B. However, I want the C column to go red if there is no value in either A or B, as in row 280, 284, 285 and 288 below. However, I can only find how to conditionally format 'blank' cells. The problem is, these cells aren't blank, they have IF formula in them referring to the data in columns D onwards. So I want C to flag red if the IF formula returns no data for either column. Sometimes the A and B (and C) will return a 0 (zero); I don't want this to trigger the conditional formatting. Any ideas?


1624608963759.png
 

Attachments

  • 1624608784924.png
    1624608784924.png
    4 KB · Views: 2
  • 1624608914670.png
    1624608914670.png
    3.1 KB · Views: 2
  • 1624608953272.png
    1624608953272.png
    3.1 KB · Views: 2

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,647
Office Version
  1. 365
Platform
  1. Windows
How about:

=AND(A1="",B1="")

or

=OR(A1="",B1="")

whether you want both or either.
 

Tristram_ZX81

New Member
Joined
Jun 22, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Thanks for the reply Steve. Sadly, these haven't worked. In image below only row 13, 19 and 27 should be red below as everything else has a 0 figure or higher in it.

1624614738234.png
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,647
Office Version
  1. 365
Platform
  1. Windows
Looks like you need to use the AND not the OR
 

Tristram_ZX81

New Member
Joined
Jun 22, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sorry Steve that's not quite working either

1624615258138.png
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,647
Office Version
  1. 365
Platform
  1. Windows
Tell me exactly what the CF formula says and what its applied to. Its impossible for what i gave you to format like that. Is there other CF on those cells?
 

Tristram_ZX81

New Member
Joined
Jun 22, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks Steve. Cell A4 is

= IF(ISNUMBER(SEARCH("football",Q4)),0.1, IF(ISNUMBER(SEARCH("rugby",Q4)),0.1, IF(ISNUMBER(SEARCH("tennis",K4)),0, IF(ISNUMBER(SEARCH("quidditch",K4)),1, IF(ISNUMBER(SEARCH("golf",K4)),1, IF(ISNUMBER(SEARCH("nfl",K4)),0.5,""))))))


Cell B4 is the same but with different sports. (The above is a simplified version, there's actually 64 IF conditions in the actual formula).

Cell C4 is =IF(OR(A4=0.1,B4=0.1),0.1,IF(OR(A4>=0.2,B4>=0.2),SUM(A4:B4),""))
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,647
Office Version
  1. 365
Platform
  1. Windows
Its not the formulas in the cells i would need. Its the conditional formatting formula you are using and what its applied to. The formula i gave you is to be used for conditional formatting.
 

Tristram_ZX81

New Member
Joined
Jun 22, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Ah. The only conditional formatting I've used is the codes you gave me, I tried

=AND(A1="",B1="")

and then

=OR(A1="",B1="")

The above screen shots are the results of using conditional formatting formula.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,579
Messages
5,770,958
Members
425,653
Latest member
UNSING

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top