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
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: 9 months ago.
Category: Microsoft Office
Expert:  Russell H. replied 9 months ago.

Hi, thank you for contacting JustAnswer.com. 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.

Thanks.
Customer: replied 9 months 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 9 months 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 9 months 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 9 months 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 9 months 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 9 months 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. http://www.excelbanter.com/showthread.php?t=110955 and
http://superuser.com/questions/474363/how-to-change-the-text-color-by-a-functions-in-excel-2010 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: http://www.techonthenet.com/excel/questions/cond_format8.php )

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
=isRed(A1)
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 9 months ago.

Hello,

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,

Rachel

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

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    270
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    260
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    120
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    116
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    94
    Bachelor's Degree in Information Technology, Microsoft Certified Professional