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
138 Views
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
Question by:zepold

LVL 24

Accepted Solution

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

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

### Suggested Solutions

Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
A Short Story about the Best File Recovery Software – Acronis True Image 2017
Viewers will learn the different options available in the Backstage view in Excel 2013.
Viewers will learn the basics of the new Quick Analysis feature in Excel 2013.