CODEWITHSUNDEEP
excelvbarowshighlight
user1217506
user1217506

Reputation: 11

Excel vba - skip rows already highlighted

The following excel vba code compares dates in top row with start and end dates in columns E and F. It then highlights the cells to produce a colour bar in each row.

How can I skip rows already highlighted and automatically create the coloured bar after a valid date is entered? Thank in advance for any help you can provide.

Set Rng = Range(Range("E7"), Range("E" & RowS.Count).End(xlUp)) 'The start end dates are in columns E and F
DateRng.Resize(Rng.Count + 1).Interior.ColorIndex = xlNone

For Each Dn In Rng
    For Each Ac In DateRng

        If Ac >= Dn And Ac <= Dn.Offset(0, 1) Then
            Ac.Offset(Dn.Row - 2 - 0).Interior.ColorIndex = Range("D4").Value
            Ac.Offset(Dn.Row - 2 - 0).Borders(xlEdgeTop).Color = vbWhite
            Ac.Offset(Dn.Row - 2 - 0).Borders(xlEdgeTop).LineStyle = xlContinuous
            Ac.Offset(Dn.Row - 2 - 0).Borders(xlEdgeTop).Weight = xlThick
            Ac.Offset(Dn.Row - 2 - 0).Borders(xlEdgeBottom).Color = vbWhite
            Ac.Offset(Dn.Row - 2 - 0).Borders(xlEdgeBottom).LineStyle = xlContinuous
            Ac.Offset(Dn.Row - 2 - 0).Borders(xlEdgeBottom).Weight = xlThick
        End If
    Next Ac
Next Dn

V

Upvotes: 1

Views: 736

Answers (1)

Pynner
Pynner

Reputation: 1017

My solution (and there are certainly a number of different ways to solve this). would be create a style (or styles) that contain the formatting you want to use.

Then anywhere in your code where you want to skip/do based on formatting you can simply ask if the style (or styles) are valid for the current cell by doing:

If Ac.style = "MyStyle" Then
' do stuff
Else
' do other stuff
end if

To check for multiple different styles you can use a collection or scripting dictionary to test all styles via a function. let me know if you would like further detail on how that works.

Upvotes: 2

Related Questions