Coloring Excel rows dynamically 2

[sweetleaf]

hi there!

I was wondering if anyone knows how to (in code) set a rows color to say grey for rows which have no data on them - the grey - colored rows , however, must start from column C and extend to column AD.

Any suggestions would be highly appreciated!

 

[oharab]

use conditional formatting & a custom function.

Put this in a module:

Function AllBlank(rng As Range) As Boolean
Application.Volatile
Dim cl As Range
AllBlank = True
For Each cl In rng
If Len(cl.Value) <> 0 Then AllBlank = False
Next cl
End Function

This function takes a range of data and returns true if all cells in the range are blank.

Back on the worksheet:
select the area you want to be gray if the row is blank (you can select the whole row)
Got to Format->Conditional Formatting
Change the drop down list to &quot;Formula Is&quot;
In the formula bar put:
=allblank(3:3)
replacing 3 with the row you are on, or change 3:3 to the range you are testing
Change the format to however you want it and BINGO, it's done.

Hope that's what you want.

ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[ktwclark]

You would have to use a For...Next loop to determine the rows to process:

For CurrentRow = 1 to 5 'Process row 1 through 5
....
Next CurrentRow

Within the loop you would have to check the contents of the cells in question and compare to an empty string. The cells could be anything from numbers to dates to strings to nulls. Easiest way is to use strings and the condition IF...Then...Else:

If (&quot;&quot; & Range(&quot;A&quot; & Trim(Str(CurrentRow))).Value) <> &quot;&quot; Then
.....
End If

The contents of cell Ax, where x is 1 through 5 depending on state of loop counter, are concatenated to an empty string, thus if the cell contains any variable type other than a string it will be converted to a string. Then this string is compared to an empty string, &quot;&quot;. If the the results are the same, i.e. the cell contains nothing then the code in between the If...End If is executed.

Rows(&quot;C&quot; & Trim(Str(CurrentRow))) & &quot;:AD&quot; & Trim(Str(CurrentRow)))).Select
Selection.Interior.ColorIndex = 55

The Rows... will select the CurrentRow but only cells C through AD. The Trim is used to remove extra spaces when converting numbers to strings, the Str converts a number to a string.

The Selection... changes the colour index of the interior part of the selected cells to 55, which is red.

It isn't the exact code you're looking for, but it's enough to get you on the right track. No fun in just giving you the answer, I always find a gentle nudge is always better.

 

[sweetleaf]

thanks so much guys!

I will try this approach and keep you posted on how it turns out.

 

[Loomah]

As always, there's more than one way to skin a cat!

So here's my effort

Sub emptyRows()
Dim r, c
For Each r In ActiveSheet.UsedRange.Rows
Set c = r.EntireRow.Find(&quot;*&quot;, LookIn:=xlValues)
If c Is Nothing Then
Range(Cells(r.Row, 3), Cells(r.Row, 30)).Interior.ColorIndex = 15
Else: Range(Cells(r.Row, 3), Cells(r.Row, 30)).Interior.ColorIndex = xlColorIndexNone
End If
Next r
End Sub


This will only highlight rows that are completely and totally empty and will remove shading if your data has changed.

;-) If a man says something and there are no women there to hear him, is he still wrong?
&quot;...Three Lions On A Shirt...&quot;