Link to home
Start Free TrialLog in
Avatar of dwe0608
dwe0608Flag for Australia

asked on

Conditional formatting of rows

Hi guys

a quick question - I have 3000 rows of data. I need to highlight the data to make it look like a bank statement using two colors. Starting at row A5 to the end of the data in the rows. I would prefer to see how to do this using VBA rather than any other way.


Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Change your range as per your choice

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

      If Not Intersect(Target, Range("A1:A10")) is Nothing Then

            Select Case Target

                  Case 1 To 5

                        icolor = 6

                  Case 6 To 10

                        icolor = 12

                  Case 11 To 15

                        icolor = 7

                  Case 16 To 20

                        icolor = 53

                  Case 21 To 25

                        icolor = 15

                  Case 26 To 30

                        icolor = 42

                  Case Else


            End Select


            Target.Interior.ColorIndex = icolor

      End If

End Sub
Avatar of dwe0608


Great answer ... thanks

Is there a neat way to see what the color indexes are ?


Which version of Excel?
Here's a simple add-in for 2003 to list the 56 available colours and their indices.
Avatar of dwe0608


thanks greatly that does the trick...