Greenbar Look Report

[austenr]

Hi everyone,

Sorry I have not been posting too much lately.

I have a need to create a "greenbar" look report. What I was hoping to accomplish is this:

Highlight only the rows and columns that have data in them. For example, stop at the last row that has data in it as well as the column.

I can do this with Conditional Formatting but would like it to be a macro since I work with a lot of EXCEL challenged people.

Any ideas on how to get started? Thanks

[Jacob Hilderbrand]

You want to color the entire row if there is data in any cell in the row, correct?


Option Explicit

Sub ColorRows()
Dim RngText As Range
Dim RngFormulas As Range
On Error Resume Next
Set RngText = Cells.SpecialCells(xlCellTypeConstants, 23)
Set RngFormulas = Cells.SpecialCells(xlCellTypeFormulas, 23)
On Error GoTo 0
If Not RngText Is Nothing Then
RngText.EntireRow.Interior.ColorIndex = 4
End If
If Not RngFormulas Is Nothing Then
RngFormulas.EntireRow.Interior.ColorIndex = 4
End If
End Sub

[austenr]

Hi DRJ..Seems to color all of the rows instead of every other one....Found this on another site and it works but would like for it to select only the columns and rows with data then apply the macro.

Sub ApplyGreenBarToSelection()
n=0
For Each VisRow In Selection.Resize(, 1).SpecialCells(xlCellTypeVisible)
n= n = 1
If n Mod 2 = 0 Then
Vis.Row.EntireRow.Interior.ColorIndex = 34
End If
Next VisRow
End Sub

[Jacob Hilderbrand]

Ok, I see what you want now.


Option Explicit

Sub ColorRows()
Dim LastRow As Long
Dim LastCol As Long
Dim i As Long
LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
For i = 1 To LastRow Step 2
Range(Cells(i, 1), Cells(i, LastCol)).Interior.ColorIndex = 4
Next i
End Sub

[austenr]

Works flawlessly as usual. Thanks a bunch!!!

[Jacob Hilderbrand]

You're Welcome :beerchug:

Take Care