excel vba - cell formating

davisto · Sep 4th 2003

Looking for a little syntax help.

I'm writing an excel module that will scan each cell for a date. If the date is not equal to today's date then I would like to highlight the entire row grey.

So far I was thinking something along the lines of the follwing:
Because this is my first time doing vba (I was a java programmer) I will have to use psuedo code.

Sub todayStandsOut()
If Sheet1.(Range("Current Cell")) != date(today) Then
Rows("current row").Interior.ColorIndex = 15
End If
End Sub

Please help!
Also If anyone knows an excel vba command reference site or listing that would be helpful as well.

Thanks

Richie(UK) · Sep 4th 2003

Hi d,

Welcome to the board

Is this the sort of thing that you had in mind?

Code

Sub DateCheck()
    Dim rngToTest As Range, rngCell As Range
    
    With Sheet1
        Set rngToTest = .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
    End With


    For Each rngCell In rngToTest
        If IsDate(rngCell.Value) Then
            If Not rngCell.Value = Date Then _
                rngCell.EntireRow.Interior.ColorIndex = 15
        End If
    Next
    
End Sub

Display More

As regards the VBA commands, the VBE Help files are a very good source of information. If you are examining code for learning purposes and want help on a partyicular item, just highlight it and press F1 for help on the subject (if it exists!). You light also wish to have a look at the various VBE examples that Dave provides on the main OzGrid site, there is some good stuff there.

If you have any specific queries then feel free to post them here.

HTH

Anonymous · Sep 4th 2003

Just a quick sidebar: Do you need to do this in VBA? You could use Conditional Formatting to do this for you, which probably would make your code run a bit faster if that is an issue.

Also, see the tutorials that Dave has posted here on OzGrid for lots of sample code. They are organized by topic in teh following link.

http://www.ozgrid.com/forum/viewthread.php?tid=4583

davisto · Sep 5th 2003

Thanks a lot guys!

I appreciate all of the help so far.

Thom thanks for the link. That should help me with my excel VBA.
= )

davisto · Sep 5th 2003

In the end I decided to go with this ...

Sub dateCheck()

'standardize the date column
'makes it easier to match criteria later
Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy h:mm:ss AM/PM"

'Dim datedigits As String
Dim A3 As Date

'G2 is an arbitrary cell. The entire column will be hidden later.
Range("G2") = Range("A3")

Dim Bcell As Range
For Each Bcell In Range("a3", "g500")
If Left(Bcell, 3) = Left(Range("G2"), 3) Then
Bcell.EntireRow.Interior.ColorIndex = 15
Bcell.EntireRow.Font.Bold = True
Bcell.EntireRow.Font.Size = 9

End If
Next Bcell

Columns("B:B").ColumnWidth = 9
Columns("G:G").Select
Selection.EntireColumn.Hidden = True
End Sub

I was asked to highlight everything that DOES NOT meet the criteria (ie not today's date) but for some reason, when I tried to use <> in the if statement EVERYTHING was highlighted.
I guess my boss will just have to settle with the opposite of what she asked for.

THanks again.

D

excel vba - cell formating

Participate now!

Share