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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now