Excel: format a row according to the value of one cell

This is about a desktop software and it might be strange to feature it as the second article of a so-called System Admin blog.  But let’s be honest: who among us doesn’t use Excel on an extensive basis, to track down their jobs, and even sometimes as some sort of light database?   We know there are more professional tools, but still, Excel has been forced into our Windows-corrupted minds as the spreadsheet tool, every Microsoft client has it, it works fine with SharePoint (yeah we have that in our company as well), so let’s skip the rhetoric and get to the point.

I want a whole line (technical name is “Row”, we’ll try to use it from now on) in a specific formatting (color, font, whatever) according to the content of one specific cell from it.   Typically, I type “OK” in the cell C2, and I want the whole row (2) to be e.g. grayed out.

So let’s use a scenario to illustrate this: I’m moving servers from one monitoring infrastructure to another, and I want to keep track of which server has been actually migrated:

See? I type “moved” and the whole line gets grayed out.

Go to the Conditional Formatting Rules Manager :  Home > Conditional Formatting >  Manage Rules (path is in Excel 2010).

Click on the New Rule button > Use a formula to determine which cells to format:


Click the Format button and choose what kind of formatting you want. Here, I chose to fill the cells with dark gray color.

In the Formula field, enter the following:


With two “=” signs, yes, and where “X” is the column where you want to test the value, and TEXT the text you want to check.  This is a logical test, if you know what it is, I don’t have to explain. If you don’t know, well I don’t want to explain, go read some.

Validate it and you are brought back to the Conditional Formatting Rules Manager where you see your rule, and you have to correct the “Applies To” field to the whole document.

(Technical Note: for ease’s sake, that’s why you use 1 as row in the formula above, you could select from a specific row but then you would have to use more complex ranges for the “Applies To” field here).

Easiest way: click on the little button next to the field and then in the upper left button (next to the A column) to select the whole document:


Or just write the “=$1:$1048576” formula in the field (yes, smartasses, you can apply this to any rows you want…).  Let’s say we use this formula, which also explains why we used the first row ($X1) in the formula above:  you want the value of the cell to impact the formatting on same line of data.

Now write your text in the corresponding cell. For the example here “moved” (or “Moved”; this is Windows, not case sensitive), and bam, the whole line gets greyed.
Now this is nice, don’t you think? Well, don’t be happy too soon. Cause if you manipulate your cells with actions that impact style (cut-paste with style, row insertions, etc.) the “Applies To” might get severely screwed:



And this is just after two cut-pastes and two insertions.

As usual, Microsoft Office products managing styles & layout for you; be prepared to correct this.

In short, when you got something weird with your conditional formatting, just review the Rules Manager, be sure to select “Show formatting rules for: This Worksheet” (unlike in the last screenshot above…), erase all the crap in the “Applies To” field and enter the magic formula for the whole document again  (=$1:$1048576) or just click the button left to the A column, like explained above.

This will clear the mess.   And to (try and) avoid these kind of stuff, in a document with lots of formatting like this, think about copy-pasting VALUES from the cells, instead of the cells themselves.

What is locking my file or folder?

We’ve all had this.  You want to delete or rename some file or folder, and you get the very dreaded following window:

You probably already know about the locked files and checked the Server Manager > Roles > File Services > Share and Storage Management > OpenFiles   (this is the path for Win 2K8).  Your folder is not to be seen, and it gets you mad, I know.

Then you Google some, and you find some miraculous third-party software which-will-solve-all-your-problem-but-please-click-next-next-next-and-don’t-read.

Delta Toolbar?  SRSLY?

Then let’s think: it’s Windows, we are in 2015; what about Powershell?

Beam Us Up Scotty proposes a command for this, let’s try it on that example directory:

It might be actually good for files, but in the case of a directory, it returns nothing 🙁

Out of the box, I haven’t found a way to get what I want:  clearly identify what the hell is locking my file or folder.

There is a Sysinternals executable called Handle.exe (which I would love to decompile some day and inject its commands into some PS!) which looks promising.

Note that you can run this executable from a remote location, but not from a certain location, aimed toward a remote machine where the file is locked.

The Lonely Administrator and Stackoverflow propose solutions using this, let’s try:

  1. Download Handle.exe from the official Sysinternals site. It’s a safe lonely executable, no crappy toolbar, no adware and the like.
  2. Put it on some network share.
  3. Write a nice PS function to use Handle.exe. As we won’t reinvent the wheel; just grab the one from the Lonely Administrator (the second “Click to Expand Code”)… it’s well done and works fine (*).
  4. Put the function in a file (let’s call it Get-LockingProcess.ps1) on a share (e.g. the same as Handle.exe, let’s call it MYSHARE for the example).  Be sure to edit the path to the Handle.exe file (variable $Handle in that code) to match your network share.
  5. From anywhere where a file or folder is locked, as long as you have access to the said share, you open a Powershell window, you include the Get-LockingProcess.ps1 (using the dot command for instance) and then you call the function with the locked directory.

Let’s try this with our example dir.  I put Handle.exe and the Get-LockingProcess.ps1 files on \\MYSHARE:

Pretty clear: you have the directory opened in a cmd window!

This is from a system admin’s point of view and wanting to give any power user a quick and easy way to identify locked files/folders.
Of course you can also do this locally, and/or only output Handle.exe to a file and Ctrl+F into that file, but it isn’t as fancy ! 🙂


(*) For future’s sake, here is a copy, with cited source:

Doesn’t that regex totally PWNS? 🙂