0

I am new to VBA and, most of the time, I either find a code here or record a macro then change it for my needs. This time I couldn't find a proper code to change thus I need your help.

Assume that I have a table as below;

      A       B      C
   Account  Name  Surname
 1   111      AA    BB
 2   111      AA    BB
 3   111      AA    BB
 4   222      CC    DD
 5   333      EE    FF
 6   333      EE    FF

I want to fill the entire row with different colors (ie. 2 colors like a table formating) if "Account" column contains same values. Here, for example, rows 1-2-3 will be red, row 4 will be green, rows 5-6 will be red again. When the macro reaches the last cell it will stop.

I tried to modify conditional formatting codes however I couldn't manage it. They generally work on cells in a column based on some criteria.

Any help will be appreciated.

Many thanks in advance!

Improve this question
2
  • Typically the Stack Overflow community likes to see some code already worked out before you come ask questions, but I'll do my best to get you started. Try playing with some of the suggestions I give below. Commented Jun 19, 2018 at 12:11
  • Is there a reason you need to use VBA for this? It could be easier without. Commented Jun 19, 2018 at 12:35

3 Answers 3

Reset to default
0

A simple If-Then Statement seems to be all you need. Try to follow what this code is doing below:

Sub ColorCode()

Dim thisAccount, lastAccount As String
Dim NumRows, colorValue As Integer
Dim isFirstRow As Boolean

NumRows = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
lastAccount = ""
isFirstRow = True

With Sheets("Sheet1")
    For i = 1 To NumRows
        thisAccount = .Range("A" & i).Value
        If isFirstRow = False Then
            If thisAccount <> lastAccount Then
                If .Range("A" & i - 1).Interior.ColorIndex = 3
                    .Range("A" & i).EntireRow.Interior.ColorIndex = 4
                Else
                    .Range("A" & i).EntireRow.Interior.ColorIndex = 3
                End If
            Else
                colorValue = .Range("A" & i - 1).Interior.ColorIndex
                .Range("A" & i).EntireRow.Interior.ColorIndex = colorValue
            End If
        Else
            .Range("A" & i).EntireRow.Interior.ColorIndex = 3
        End If
        lastAccount = .Range("A" & i).Value
        isFirstRow = False
    Next
End With

End Sub

As opposed to the previous answer, this should account for the first row by using the initial If-statement asking if it's the first row, and because we change it's value later, the only time it will run is on the first row.

Simply put, I loop through the total number of rows in the data, ask it if the account number is the same as the previous one, and if it is, I color code them the same, and if it's not, I color code them different. I just ran this myself with some made up data and seems to work perfect.

Happy coding and next time try to ask a more code specific question!

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

You can use either the ColorIndex command or the RGB values when filling the interior of cells. You can find the link for all of the color index values here. The RGB values are best found by simply opening MS Paint and playing with the RGB values there
Thank you Matt it worked! ,Next time, I will post my code for you to comment on it, promise. :) Many thanks!
No worries. I'm glad to help you get started!
0

Conditional formatting is still the way to go here.

I have to point out, that your coloring logic ("color row in respect to previos cell in row A") does not apply to the first row, and thus has to fail.

If you absolutely need to use VBA, this is the logical path you want to follow:

  1. set column A as a range
  2. for each cell in this range, compare this cell to the previous one (again, row 1 does not work here)
  3. color the row based on this comparison

If you need help with the coding, update your question with what you have tried so far.

Improve this answer

1 Comment

Thank you David, I managed to do the coloring with the help of Matt's code above. I learned the logic by your help. Cheers!
0

This can also be done without using VBA.

We can be sneaky by using RANK and MOD to identify "alternating groups", since Conditional Formatting works with any formula that returns only a TRUE/FALSE.

Using your example data (except row numbers adjusted to account for heading):

image

Instructions:

  1. Select A2:C2; set the Fill Color to Light Red. Then with A2:C2 still selected:
  2. Home (ribbon menu) > Conditional Formatting > New Rule…
  3. Choose Use a formula to determine which cells to format
  4. Choose Format cells where this value is true and enter formula:
    =MOD(RANK($A2,$A:$A,1),2)=1
  5. Click Format > Fill (tab) > Light Green > OK > OK
  6. Ctrl+C to copy (while still on A2:C2)
  7. Select A2:C7 and Paste Special > Formats
Improve this answer

1 Comment

VBA works on the file to perform other tasks and that's why I tried to do coloring with VBA. Thank you vm for your help!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.