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
141 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
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

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 how to customize the ribbon and quick access toolbar in Excel 2013.
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: …

770 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