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:

Select the Table area to be formatted. (in this case B3:E11)
Home Tab > Conditional Formatting > Manage Rules
Select "New Rule"
Select "Use a formula to..." (refer snapshot below)
Enter the formula as < =$D3="in progress" > as my status column to be checked was D. please adapt as needed.
Select "Format" to specify the format of matching celles. I selected Blue color in "Fill" Tab. (refer Snapshot).
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

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..

[ Read more » ]

Controlling LED through Octolapse for 3D print timelapse

Today's post is about one of the 3D Printing adaptation that I learned. This is specifically about how to control an LED light automatically through OctoLapse so that it is switched on only when the timelapse photo is taken. Just as a background, I have been learning to use 3D printer and its customizations options for a few months now and was a lot of learning since then. The best thing was OctoPi, a platform for controlling the 3D printer and the many plugin options that are developed by the community. Most of them are like open source. Today we will discuss specifically about an adaptation to one such plugin, OctoLapse. OctoLapse is the plugin for taking timelapse photos, with many options for further customisations. One of the best timelapse method is taking photo after each layer is complete, by moving the head out of the way. You can use standard Pi Camera or a DSLR, which is great to get good resolution videos. One small flash back before we dive into the topic. I keep the

[ Read more » ]

PTC Creo | my Mapkeys for free

I have created a list of frequently used mapkey shortcuts for the PTC Pro Engineer Creo. This is the macro equivalent in creo. I am copying them below.If you need them, copy paste the content to the "config.pro" file in your startup folder. My favourites are highlited and greatly improves the workflow.. For ex, to reach MEASURE. need to go to another menu and click. Instead, maypkey from any selected menu on the ribbon will work.. Thats wonderful to me... Also, Edit Sketch (ES) is overloaded and will work for Extrusion, Revelution, Sweep etc.. So is aa, pp, zz.. really helps me a lot.. Hope you will start using them as well and get benefited! Let me know in comments, your feedback and issues.... Sketch View > sv Show and Erase > se Working Directory > wd Hiddel Line View > hi Close (quit) Window > qw Measure > mm Erase Session > ee Insert Mode @ Sel > is Cancel Insert Mode > ic edit Sketch

[ Read more » ]

Tips by Joe (Proe, Excel...)

Search This Blog