How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask NewITZone Your Own Question

NewITZone, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 90
Experience:  I have been using Office Pro for 10 years and implemented applications for business use.
Type Your Microsoft Office Question Here...
NewITZone is online now
A new question is answered every 9 seconds

In MS Excel, what is a formula to test if a cell in a range

Customer Question

In MS Excel, what is a formula to test if a cell in a range has the color red?  I don't know if this makes a difference.  But if the cell is red, it will be so by conditional formatting.

Submitted: 3 years ago.
Category: Microsoft Office
Expert:  Russell H. replied 3 years ago.

Hi, thank you for contacting My name is Russell. I will do my best to provide the right answer to your question.

Please tell me what version - or version range, if several different versions are involved in your question .... - of Microsoft Excel you are asking about.

Please specify whether the background of the cell's color is to be tested for, in the formula, or the foreground i.e. text color, in the cell. It makes some difference.

Customer: replied 3 years ago.

I am testing for red background color, and if it is present in any cell in a given range, then I want another cell to also change to red.

Say, if any cells in range G12:G45 are red then cell A1 must also turn red. I want to use conditional formatting for A1. All Cells in G12:G45 also have a conditional formatting to turn red if certain criteria are met. I don't know the color index number for the red that is used. Perhaps there is a way to find out.

Thank you


Expert:  Russell H. replied 3 years ago.
So A1 is the cell that reacts, and it reacts to *any* cell being red-backgrounded?

Or how does the cell-that-reacts relate to the cell(s)-that-is/are-checked ?

And you want a conditional reaction in G12:G45 to be passed on to A1 (or some other cell too in other words) ?
Customer: replied 3 years ago.
Ok, so am deciding to take a different approach on my spreadsheet.
Let me begin anew;

I would like cell A1 on "Cover Page"! to turn red if any cell in range Sheet1!J12:J45 has the text "red".

I want to use contional formatting, but I can't come up with the right formula.

Also, on cell A2 on "Cover Page"! I want it to turn red if Sheet2!J12:J45 has the text "red".

So essentially, I want to construct the formula in a way that I can fill down from A1 to A20 on "Cover Page"!, I want A1 to check Sheet1, A2 to check Sheet2, A3 to check Sheet3, and so forth.

I don't want to have to redo the formula for each cell in column A, so I want the formula in a way that I can just drag and fill down

Thank you kindly
Expert:  Russell H. replied 3 years ago.
So in other words, Ax reacting to Sheetx!J12:J45.

I will see if I can figure out a way to do that. Also, let me know meanwhile, what Excel version you are using. Thanks.
Customer: replied 3 years ago.
I am using Microsoft office professional 2010.

Please remember that I want I be able to use this in conditional formatting. Also, I want to be able to fill down to the next cells, without redoing the formula for each one. Thank you kindly. Also, I have my sheets named 0001, 0002, 0003, and so forth. I don't know if excel sees thes worksheet names as text, if that is an issue. I am rename then sheet1, sheet2, and on and on, that's not a problem

Thank you kindly
Expert:  Russell H. replied 3 years ago.
I have looked into the issues in your question, and found that, unfortunately,

1. testing for color can't be done with a formula - you would need VB script code for that.
(Ref. and which explain that the VB property ColorIndex can be tested by VB code, or can be set by VB code.)
To do this task by VB code instead of with a formula, you create a VBA macro, save it in the workbook, and then you build a formula that uses that macro.

2. changing the color in a cell in reaction to some condition as determined by a formula can be done only by 'Conditional formatting', apparently - which is not the easy 'fill with the same formula' process you are looking for, as it apparently needs to be done one-cell-by-one-cell (though I am not certain of this, not certain that it is limited to one cell at a time).
(Ref: )

In short, to get the action you require, you need either VB scripting and/or Conditional Formatting.
There is a formula that is *near* what you need, in
but that tests for background color in the cell, I think.

Since this isn't an Answer, I have posted this as an Inquiry, though I haven't asked anything.

Since someone else might have the advanced comprehension needed for the task, I have Opted Out of this case.
Expert:  Rachel-Mod replied 3 years ago.


I’m Rachel, and I’m moderator for this topic. It seems the professional has left this conversation. This happens occasionally, and it's usually because the professional thinks that someone else might be a better match for your question. I've been working hard to find a new professional to assist you right away, but sometimes finding the right professional can take a little longer than expected.

I was checking to see if you had already found your answer or if you still needing assistance from another one of the professionals?

Please let me know if you wish to continue waiting or if you would like for us to close your question.

Also remember that JustAnswer has a multitude of categories to help you with all your needs from Health, Pets, Computers, Taxes, Cars, Finance, Law, to Home Improvement, and more.

Thank you,


Customer: replied 3 years ago.
Hello Rachel. I would like to close this question.
Thank you for your concern.

Related Microsoft Office Questions