Solved

How do I set the background color of a cell based on the values in a range of cells?

Posted on 2014-11-06
2
152 Views
Last Modified: 2014-11-06
How do I set the background color of a cell based on the values in a range of cells?  I have a spreadsheet with columns that have static data and a range of columns that has dynamic data.  I want the rows of static data to be highlighted if the data in that range is equal to a specific value.

For example, if the data in range D3:O3 all equal "OFFLINE", then I want row A3:C3 to be highlighted in red.
Sample.xlsx
0
Comment
Question by:zepold
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40427241
Highlight cells A3:C3.

Go through the procedure outlined in https://www.ablebits.com/office-addins-blog/2014/06/10/excel-conditional-formatting-formulas/

The formula you want in step 5 is

=$d3&$e3&$f3&$g3&$H3&$i3&$j3&$k3&$l3&$m3&$n3&$o3="OFFLINEOFFLINEOFFLINEOFFLINEOFFLINEOFFLINEOFFLINEOFFLINEOFFLINEOFFLINEOFFLINEOFFLINE"
0
 

Author Closing Comment

by:zepold
ID: 40427273
I also managed to put this formula together, this afternoon:

=(COUNTIF($D3:$O3,"=OFFLINE")=12)

Seems to work, as well, but I haven't tested it fully.  Literally, just came up with it when I got your email.
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Viewers will learn a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …
Viewers will learn the basics of the new Quick Analysis feature in Excel 2013.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question