Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
161 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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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 the different options available in the Backstage view in Excel 2013.
Viewers will learn the basics of formula auditing in Excel 2013.

705 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