Conditional formatting a row

Occasional Visitor


I am trying to format a whole row based on one cell in that row. If the cell has a negative value then it is filled green, and if it has a positive value it is filled purple. I would like this fill to apply to the full row, but I am having a difficult time figuring out how.



2 Replies


1. Select whole data range where you want to apply the formatting, e.g. A2:E100

2. Conditional Formatting/New Rules/Use a formula to determine which cell to format

3. Enter the following formula exactly into the formula field:


and click Format and choose Fill = Green color

4. Do the same steps 1-3 but enter new formula: = $E2>=0 and choose Format = Purple color


If you do all above correctly, when you go to Conditional Formatting/Manage Rules/Show formatting rules for this worksheet, you should see the below:



Hi @mkrpavel 


You should try and use the option of "use a formula to determine which cells to format"


Then use these formulae


=$E2>0 for positive values


=$E2<0 for negative values


Also ensure that the "applies to" covers the part of the row you will like to format e.g =$A$2:$E$22.


I have attached a sample file for your perusal.