Looking for how to do Conditional Formatting for the following rule:

If a number in a cell equals one of a list of particular numbers, then information MUST be entered into an additional cell. Both cells will turn RED until this is completed.

For example,

Material #   Batch    Fill   Line #
  83716      UP1278            1
  83715      UP1284            3
  83704      UP1287            4
  53716      UP1255            2
  26415      UP1291           12
  26415      UP1293           12
  56160      UP1257           10

When the cell in the Material # column contains the number 53716, 26415, or 56160, then this cell will turn RED as well as the cell on the same row that is in the Fill column. BOTH cells will remain the color RED until the cell in the Fill column has data typed into it.

How should I set up conditional formatting for this when I want to do it for different numbers?

How do I make my list of this # or this # or this # or this # .......... and have the cells in both columns respond to the particular numbers by BOTH turning RED in color until the Fill column cell has data information typed into it?

  • How do you have both the Material # and the Fill cells behave this way? Simple: assign the same conditional format to both cells. What have you tried? Have you done any research? And how are the “particular numbers” determined? Are they the same for all rows? Are they constant for all time, or might they change? And, if they can change, where will the new values come from? (I.e., are they stored in the spreadsheet somewhere?) Jul 15, 2014 at 23:26
  • I have used =$B$3:$B$14 for each of the numbers I listed in the Material column that I want to turn RED until data is entered into the Fill column ( then the RED would go away in BOTH columns ) . This makes the cells I want in the Material column turn red but I cannot get the corresponding Fill cell to also turn RED. And I cannot figure out how to make the RED stop for both cells once data is entered into the corresponding Fill column cells.
    – user346698
    Jul 15, 2014 at 23:44
  • I’m not sure what you’re saying about =$B$3:$B$14. Look into the AND() and OR() functions, and try testing $F1="" (letting F represent the Fill column). Jul 15, 2014 at 23:53
  • Keep getting error message. Still trying to get both cells to turn red if the cell containing the first number equals a certain number and the second corresponding cell in the row is blank. I will keep trying.
    – user346698
    Jul 16, 2014 at 0:55
  • You keep getting [an] error message? What error message? This is the first time you’ve mentioned an error message. … … P.S. If J_V’s answer helps you, you should accept it (by clicking on the check mark). Jul 16, 2014 at 15:21

1 Answer 1


When introducing more complex conditions it's always a good idea to split them into smaller parts to begin with and combine them later on. Here you could first make sure that the cells turn red if a certain values are found in the first column.


For the second condition you can use "" to spot an empty cell as Scott stated.


Now it's as simple as combining the two together with the AND function.



You can make the OR condition fancier by using a Range or a Named Range.


Range in the condition

Note that you may have to swap all of the ; to ,.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.