Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

How to Format AutoFill Options in Excel

Copper Contributor

I'm looking for a way to auto fill (highlight) the entire row a particular color based on the value entered in a cell on that line. For instance, I want every row highlighted blue where there's a number "7" entered in column "b" of that row, every row highlighted yellow where there's a "5" entered in column b of that row, and so on. 

 

The number entered in column b should determine the color of the auto fill (highlight) of the row. 

7 Replies
best response confirmed by ebkay82 (Copper Contributor)
Solution

@ebkay82 

Excel does not have a way to do this automatically - you have to create a conditional formatting rule for each value.

Let's say you want to apply this to rows 2 to 100. Select these 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

=$B2=7

Click Format...
Activate the Fill tab.
Select blue as highlight color.
Click OK, then click OK again.

 

Repeat these steps with the formula =$B2=5 and yellow as highlight color.

 

Etc.

Hello again - I tried that, and it seems to overwrite the formatting with the last one I entered. For instance, if I set up formatting so 3=fill in yellow, 4=fill in blue, and 5=fill in grey, it overwrites the colors for 3 and 4 with grey.

@ebkay82 

Since a cell cannot have multiple values at once, only one of the rules should be applied.

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

I've saved a sample work book here: https://1drv.ms/x/s!AiqbtuFjxl9ksQlQdPUrOIfjuurB?e=fVwPam

I need the highlight colors of the rows to change as the value in column F is changed according to the key.

@ebkay82 

Thanks. See the attached version.

I removed the fixed fill colors and created 5 conditional formatting rules.

On the Home tab of the ribbon, click Conditional Formatting > Manage Rules... to inspect these rules.

1 best response

Accepted Solutions
best response confirmed by ebkay82 (Copper Contributor)
Solution

@ebkay82 

Excel does not have a way to do this automatically - you have to create a conditional formatting rule for each value.

Let's say you want to apply this to rows 2 to 100. Select these 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

=$B2=7

Click Format...
Activate the Fill tab.
Select blue as highlight color.
Click OK, then click OK again.

 

Repeat these steps with the formula =$B2=5 and yellow as highlight color.

 

Etc.

View solution in original post