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.

MTIA

DWE
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
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

                        'Whatever

            End Select

            

            Target.Interior.ColorIndex = icolor

      End If



End Sub
Avatar of dwe0608

ASKER

Great answer ... thanks

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

Regards

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

ASKER

thanks greatly that does the trick...