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.

[VBA]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 [/VBA]

[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

Take Care