Forum Discussion
Solved
Color fill whole row based on text in certain column
I'm making an assignment tracker for my classes. I want the row to fill a certain color based on the class number (in column i). I've followed videos that show conditional formatting equations to do it and none of the equations are filling any cells with color, even when I narrow the scope of the equation. The values start in row 2 since row 1 says what the rows represent. The last things I have are points in column K. I have tried these equations so far (The format is fill light blue for all):
Formula: =$I2="315" Applies to: =$1:$100
Formula: =$I$2="315" Applies to: $1:$100
Formula: =$I2="315" Applies to: $A$2:$K$8 (I only have 8 rows filled with info so far)
Formula: =$I$2="315" Applies to: $A$2:$K$8
the first mention of 315 is in i2
I have also tried taking out the $s, ""s, and using 's instead. Those all give me errors.
I don't know if I'm using incorrect equations or what but so far nothing has worked to fill any cells
Select rows 2 to 100, or if you prefer A2:K100. The active cell in the selection should be in row 2, for example A2.
Use the following formula in the conditional formatting rule:
=$I2=315
(neither single nor double quotes around 315)
4 Replies
- I'm not sure why you are getting errors in every case but will try to help.
The 'formula' should be made as if you are making it work for the upper left cell in the Applies To range and so you can copy/fill to every other cell in that range. SO in this case I would suggest an applies to range like $A:$K or $A$2:$K$100
In the first case the upper left cell is A1 so the formula should be =($I1=315)
In the second case the upper left cell is A2 so the formula should be =($I2=315)
As for the $s that is what you want to 'lock' so you always want to look at column I but the row should change and match the row of the cell you are checking.
as for "" that "315" or 315 depends on if the values in column I are forced to be TEXT or default to NUMBER. Since the default is if you type 315 into a cell it will default as a number I left the "" off. If you force that column to be TEXT or precede the number with an ' so you enter '315 then it will be text and you need "315". Alternatively you can force the value in the formula to be text or have excel try to convert it to a number:
=(TEXT($I2,"0")="315") Select rows 2 to 100, or if you prefer A2:K100. The active cell in the selection should be in row 2, for example A2.
Use the following formula in the conditional formatting rule:
=$I2=315
(neither single nor double quotes around 315)
- This worked thank you!
- I've also tried applies to: $2:$100
