Forum Discussion

AnnaBanana's avatar
AnnaBanana
Copper Contributor
Jan 16, 2020
Solved

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

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.

 

 

  • 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.

1 Reply

  • 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.

Resources