Forum Discussion
I need to change the color of all cells in a row based on the data in a single cell
Let's say your data are in rows 2 and below, and the status is in column K.
Select those rows. The active cell in the selection should be in row 2.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=$K2="ON HOLD"
Note that there is a $ before the column letter K but not before the row number 2. This is essential.
Click Format...
Activate the Fill tab.
Select red as highlight color.
Click OK, then click OK again.Repeat these steps, but with the formula
=$K2="DONE"
and green as highlight color.
Etc. - one rule for each status.
Let's say your data are in rows 2 and below, and the status is in column K.
Select those rows. The active cell in the selection should be in row 2.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=$K2="ON HOLD"
Note that there is a $ before the column letter K but not before the row number 2. This is essential.
Click Format...
Activate the Fill tab.
Select red as highlight color.
Click OK, then click OK again.
Repeat these steps, but with the formula
=$K2="DONE"
and green as highlight color.
Etc. - one rule for each status.
Greetings Hans, Very good formula, I appreciate this, However, I ran into a problem where I don't know how it happened.
When I completed this function, and input my data, it not only changed the affected line green, but ALSO changed the line below it green too.
I've experienced this in a different sheet where it didn't really matter, but I'd like to find the "why" and a resolution. Any input?
1) Are you sure that you used a formula such as
=$D2="ON HOLD"
and not
=$D$2="ON HOLD"
2) Does your range perhaps contain merged cells?
Thank you for the speedy reply.
formula is: =$A2="completed" and =$A2="pending"
No merged cells,
The workbook derives from microsoft forms and its associated excel spreadsheet with responses. I had to add a column (column A) with its cells having data validation (drop down options either: completed or pending), This column however is not part of the table. I'm unable to paste or upload a screenshot through this forum.
The formula started to work originally but along shortly after (i believe when there was an additional form entry) it started to change the appropriate row + the row below the desired color.Could you create a small sample workbook demonstrating the problem (without sensitive data), and make it available through OneDrive, Google Drive, Dropbox or similar?