Share via

Conditional formatting in many cells, based on content in cells at same row level

Anonymous
2020-06-25T08:18:09+00:00

For example: If cell in d2 is blank --> cell b2 stays white (or whatever)

But, if d2 contains text/or a date --> cell b2 changes colour (drawing attention to the reader - it should be given a certain batchnumber)

When b2 contains a number it turns back to the original format. 

I want this conditional formatting to true for all the cells in column d and b, meaning that the same relationship applies for d3 and b3, d4/b4, and so on. 

The aim is to more easilly detect gaps in the sheet. 

Thank you in advance!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2020-06-29T07:19:52+00:00

These are the steps in quick succession.

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2020-06-25T10:23:16+00:00

Do your regional settings use the semi-colon for the system list separator? Try this version instead.

=AND(ROW($B1)>1; OR(ISTEXT($D1); ISNUMBER($D1)); NOT(ISNUMBER($B1)))

Was this answer helpful?

0 comments

Answer accepted by question author

Anonymous
2020-06-25T08:29:39+00:00

Select all of column B with B1 being the active cell then create a conditional formatting rule based upon the following formula,

=AND(ROW($B1)>1, OR(ISTEXT($D1), ISNUMBER($D1)), NOT(ISNUMBER($B1)))

Click format and choose an alarming colour (red, yellow, etc) for the Fill and then OK to create the rule.

Was this answer helpful?

0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-06-29T06:53:25+00:00

    sUCCESS!

    I tried again, like this:

    Instead of marking the entire row, I chose just one cell, using conditional formatting > manage rules > new rule > pasted the formula that you gave me and changed the row value to fit the cell I had chosen. 

    The formula worked for that one cell. 

    Then I copied the cell to the remaining cells in the column :-)

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2020-06-25T10:09:26+00:00

    Thank you for trying to help. 

    I have tried to insert this formula, but I get error every time. I can't see why.

    Was this answer helpful?

    0 comments