Highlight Row Based on Specified Columns

[dwgnome]

I would like to have Excel 2003 be able to highlight the row if there is data in certain columns. Specifically if there is a number greater than zero.

For example the following could work:

[=OR(LEN(H9),LEN(I9),LEN(K9),LEN(Q9),LEN(R9),LEN(S9),LEN(W9),LEN(X9),LEN(Y9),LEN(AB9),LEN(AD9))/CODE]

However, there are times when I need to add a column/criteria.  Thus I would have to go into conditional format and redo all the rows in the worksheet.  Is there a smarter way to do this?

I was hoping to provide a list of cell address from the header or better than that look for specific numbers in the header row and if there is a number greater than zero, then highlight the entire row.   

There is no specific pattern as to which columns would trigger the requirement.

Also how do you keep the ranges from moving when copying across and down.  Sometimes I run into that issue.  Maybe highlight the entire row and then enter the conditional format?

 

[BigC]

I'm not clear as to what you want, but I'll offer the following in any event.

To save having to amend/update your conditional formatting (CF) rules whenever you add further columns, you could use a "helper" column directly in the spreadsheet that provides the flag to the CF rule as to whether or not the row should be highlighted. That is, instead of incorporating your trigger formula in the CF rule itself, have it in a cell in the spreadsheet (it might need to be an =IF formula) to return TRUE or FALSE, Yes or No, or whatever, regarding the data in the criteria columns you wish to count, with the CF rule simply checking this result. It is easy to copy your formula and any CF format to new rows without having to amend them.

 

[dwgnome]

BigC, yes that is a good suggestion that could work. However I would prefer a more robust way of executing the CF that seems to work as shown in my OP, but need more flexibility.

Any ideas?

 

[dwgnome]

BigC, yes that is a good suggestion that could work. However I would prefer a more robust way of executing the CF that seems to work as shown in my OP, but need more flexibility.

Any ideas?

 

[BigC]

However I would prefer a more robust way of executing the CF that seems to work as shown in my OP, but need more flexibility.

That depends on what you mean by robust. My original suggestion is far easier to maintain than amending the CF each time you add a column, and is more transparent (you can see it working directly in the spreadsheet) so I beleive that is more "robust" - but that's the beaut thing about Excel, there are many ways to skin the proverbial cat!

What about this idea (which is a different take on my original one)?

1. Using row 9 as the starting point (as per your example), click on any cell in row 9.
2. In the Define Name dialogue box (I currently use Excel 2007 so am trying to remember the 2003 commands!), create a name (e.g. "CF_Trigger") with the following formula as the "Refers to:" =OR(LEN($H9),LEN($I9),LEN($K9),LEN($Q9),LEN($R9),LEN($S9),LEN($W9),LEN($X9),LEN($Y9),LEN($AB9),LEN($AD9))
3. Note that it is critical that:
   1. the row number in the formula is the same as the cell you selected when defining the name. This creates a dynamic/relative defined name that returns a result (True or False) based on the values in the row to which it is applied. (That's not a good explanation, but I can't think how else to say it!)
   2. You include the $ before the column reference to lock in the criteria columns
4. Select the range to which you wish to apply CF, and in the CF rule, just enter "=CF_Trigger" and apply the required format.

Result:

1. The Defined Name will return a separate True or False result for each row to which CF is applied (though you can't actually see this - it's in the black box!)
2. The CF rule will use the result from the Defined Name (for each row) to apply (or not) the CF to each row
3. When you add a new column, you only need to amend the "Refers To" formula in the Defined Name (one simple edit to add LEN(XXn) as another argument to the existing OR function) in order to apply the change to the whole spreadsheet.

As far as I can see, if you don't want to fiddle with the CF each time and don't want to utilise VBA (far more complex), what I've offered are your two options (i.e. formula in spreadsheet to return T/F result to the CF rule, or a formula in a Defined Name to do the same thing)

 

[Michael M]

How about this in the sheet module

Sub worksheet_change(ByVal target As Range)
Dim lc As Integer, lr As Long, x As Integer
lc = Cells(1, Columns.Count).End(xlToLeft).Column 'change to suit
lr = Cells(Rows.Count, "A").End(xlUp).Row 'change to suit
For r = lr To 1 Step -1
    x = WorksheetFunction.CountA(Range(Cells(r, 1), Cells(r, lc)))
    If x > 0 Then
        Rows(r).Interior.ColorIndex = 3 'change to suit 3 =Red
     End If
Next r
End Sub

 

[dwgnome]

Thanks BigC, your solution is clever, but a bit complex for me. Perhaps if you had a template or example spreadsheet to go by, I may be able to follow.

Michael M, I tried your solution, but nothing happened when I copied your script into a new module in ThisSheet and saved it. Maybe I am doing something wrong as I am a novice when it comes to VBA.

 

[Michael M]

The code has to go in a sheet module not a Standard (new) module
So, if you want it to work in Sheet1, that's the module you put it in.
Also remember, If you have headers in the rows and columns, you will need to modify line 6 in the code

Sub worksheet_change(ByVal target As Range)
Dim lc As Integer, lr As Long, x As Integer
lc = Cells(1, Columns.Count).End(xlToLeft).Column 'change to suit
lr = Cells(Rows.Count, "A").End(xlUp).Row 'change to suit
For r = lr To 1 Step -1
    x = WorksheetFunction.CountA(Range(Cells(r, 1), Cells(r, lc)))'checks from col "A" to last col and row 1 to last row....change as required
    If x > 0 Then
        Rows(r).Interior.ColorIndex = 3 'change to suit 3 =Red
     End If
Next r
End Sub

 

[dwgnome]

Okay, I do have headers and on the left a column of id numbers. I want all cells within the table and row to be highlighted based on a list. Does your script look for a list, compare then if true changes the cell background to a color?

I don't know how to edit the code as I do not understand VBA.


[TABLE="class: grid, width: 433"]
<tbody>[TR]
[TD]TANK NO.
[/TD]
[TD]Nitric Acid
[/TD]
[TD]Sulfuric Acid
[/TD]
[TD]Hydrochloric Acid
[/TD]
[TD]Hydroflouric Acid
[/TD]
[/TR]
[TR]
[TD]CAS
[/TD]
[TD]7697-37-2
[/TD]
[TD]7664-93-9
[/TD]
[TD]7647-01-0
[/TD]
[TD]7664-39-3
[/TD]
[/TR]
[TR]
[TD]specific density
[/TD]
[TD]1.40
[/TD]
[TD]1.84
[/TD]
[TD]1.16
[/TD]
[TD]1.18
[/TD]
[/TR]
[TR]
[TD]density (lb/gal)
[/TD]
[TD]11.68
[/TD]
[TD]15.35
[/TD]
[TD]9.67
[/TD]
[TD]9.84
[/TD]
[/TR]
[TR]
[TD]32
[/TD]
[TD][/TD]
[TD]25%
[/TD]
[TD][/TD]
[TD]4%
[/TD]
[/TR]
[TR]
[TD]33
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The first row to be highlighted is Tank 32, with light blue because there is a number in the column for sulfuric acid and in my real spreadsheet that column is I and the Tank 32 row is row 9.

Thanks again.

 

[BigC]

Thanks BigC, your solution is clever, but a bit complex for me.

Complex??

All you need do are two fairly simple steps:

1. create a Defined Name (say "CF_Trigger" as described), which in Excel 2003 is achieved via Insert / Name / Define, and
2. amend your existing CF rule to replace the long =OR(...) formula (which is now assigned to the Defined Name) with "=CF_Trigger"

All this does is create an intermediate step in the CF process, but that step provides the flexibility and simplicity (to edit as your database expands) you desire.