Skip to main content

Color the Excel row based on a value/status

Very often, we would like to differenciate the rows that are having certain status (or specific values in a specific column) in a different color.

We can filter for the status and apply the color manually, but we can also do that dynamically or automatically with a specific tweak to the Conditional Formatting feature in Excel


I am referring to Excel 2016 but very well applicable to earlier or later versions of excel.

I hope you are familiar with Conditional formatting. Its in Home Tab 

Steps involved:

  1. Select the Table area to be formatted.  (in this case B3:E11)
  2. Home Tab > Conditional Formatting > Manage Rules
  3. Select "New Rule"
  4. Select "Use a formula to..." (refer snapshot below)
  5. Enter the formula as < =$D3="in progress" > as my status column to be checked was D. please adapt as needed.
  6. Select "Format" to specify the format of matching celles. I selected Blue color in "Fill" Tab. (refer Snapshot). 
  7. click OK on all three windows and apply the formatting.

Key Snapshots:

The before


The after

The Formula

The Fomat Selection

Some Additional Explanation:

Let's have a look at what happened.

two things are importatnt.  
1, the first row of selection and 2, the column specified in the formula.
  • In this case the first row of selection is Row 3 and the column where the status to be checked is column D.
  • Hence the first cell for comparison is D3
  • The $ sign before D (in $D3) is improtant as that makes the column an absolute reference (locked).  Click here if you would like to know more how $ works in a formula.

Excel applies the formula for each cell of the selection.  lets look some random cases..
  • for cell B3, the checked cell is D3
  • for cell C4, the checked cell is D4
  • for cell E4, the checked cell is D5
In short, since we used $D in the formula, the for any row that is checked, its corresponding D columns will be the one that will be compared.

Footnote:

You can repeat the steps if you want another color for another status.
You may also use other formula like >, <, >=, <= with numbers to set format based on numeric values.

I hope you are clear how it works and having the row formatted based on a value is specific column will be easy for you..  

Feel free to leave a comment if you have issues or have any question..  Would be happy to help.

Comments

Popular posts from this blog

TreeView Structure in Excel VBA

TreeView is extreamly useful in specific cases but it can be bit tricky sometimes to implement.  Last Few days I was working on a TreeView Structure and thought I will share the knowledge I gained...  This post takes you through the basic operations to create and operate a TreeView. It will be like the Folder tree window of the windows explorer.  Moreover, you can have it dynamically updated based on the data in excel.... Here we go..

Excel Function :TABLE

Its been a while since I have done some posting. This option in excel is something which I thought would be very useful for many. Its the TABLE Function in MS Excel. This is especially useful in the following scenarios.