SOLVED

Conditional Formatting a Block of Cells Based on a Single Cell's Data

Occasional Visitor

I have an excel workbook that I created for my employees monthly work schedules; in it I track their scheduled hours, actual worked hours, pay, overtime, etc.  I have already created a drop down list where I can select which position a certain employee will be working on a specific day and it will color code to a certain color for easy info dissemination.  What I want to do, and haven't been able to figure out, is how to format an entire day to shade red when I select the word"meeting" from the day of the week drop down list I have at the top of each day of the work week.  I can shade the cell containing the word "meeting," but i'd like the entire block of cells that make up that day to be shaded.

 

I tried using the following formula when creating a new conditional format rule: =if($b$1="meeting",b1:d10)

and, while some cells were formatted correctly, the majority weren't.

 

What I want is for the range b1:d10 to be shaded light red with dark red text if cell b1 contains the word meeting.

 

 

1 Reply
best response confirmed by Abiola1 (MVP)
Solution
Hello,

A sample file would be needed to have a clear picture.

However, let say you have "meeting" and "no meeting" in cell J1 as List Data Validation dropdown and your range of data starts from A2:D100 and column A contains, for example, "Meeting" and "No meeting"

To highlight all the rows of data that has meeting based on column A:

1. Click on the New Rule
2. Use a formula...
3. Execute: $A2=$J$1
4. Apply desired format
5. Click OK.