Solved: set row color based on value of a cell

[Dodgeitorels]

I have a userform that will add data to my worksheet. this works fine. When this userform adds a new row to worksheet, I would like a vba code to set the new row color according to the value in column 2 of new row. Can someone help me please.

........cola colb colc cold cole colf colg
row1 blah blah blah blah blah blah blah
row2 blah blah blah blah blah blah blah
row3 blah blah blah blah blah blah blah
new4 blahFFFF blah blah blah blah blah

where F is the cell to determine color.

[nst1107]

By "adds a new row" do you mean something along the lines of Rows.Insert, or do you mean the userform fills a row of data?

[Dodgeitorels]

what I mean is:

nextrow = Cells.Find(what:="*", searchdirection:=xlPrevious, searchorder:=xlByRows).Row + 1
Cells(nextrow, 1).Select
ActiveCell.FormulaR1C1 = "=ROW()-1"


then rest of code fills cells across 17 columns in this new row and I want value of second column to determine color of row. Value of cell in second column is either a, b, c, d, e etc.
I will have 3 colors meaning "A" will be say yellow, "B" will be blue, "C" will be green, then colors will start again with "D" will be yellow, "E" will be blue and so on.

[Dodgeitorels]

Ok, I can get it to set color but it goes through all rows of worksheet which takes some time. Perhaps something in my code can make it just set color of the newly added data. This is the code I use right now which colors all rows with data on worksheet.

[VBA]Dim LRow As Integer
Dim LCell As String
Dim LColorCells As String
'Start at row 2
LRow = 2

'Update row colors for 1000 rows
While LRow < 1000
LCell = "B" & LRow
'Color will be changed in columns A to AL
LColorCells = "A" & LRow & ":" & "AL" & LRow

Select Case Left(Range(LCell).Value, 1)

'Set row color to light yellow
Case "."
Range(LColorCells).Interior.ColorIndex = 19
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light blue
Case "0"

Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light blue
Case "1"

Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light blue
Case "2"

Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light blue
Case "3"

Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light blue
Case "4"

Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light blue
Case "5"

Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light blue
Case "6"

Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light blue
Case "7"

Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light blue
Case "8"

Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light blue
Case "9"

Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid



'Set row color to light green
Case "A"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 35
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light yellow
Case "B"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 19
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light blue
Case "C"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid


'Set row color to light green
Case "D"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 35
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light yellow
Case "E"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 19
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light blue
Case "F"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light green
Case "G"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 35
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light yellow
Case "H"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 19
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light blue
Case "I"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light green
Case "J"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 35
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light yellow
Case "K"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 19
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light blue
Case "L"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light green
Case "M"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 35
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light yellow
Case "N"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 19
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light blue
Case "O"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light green
Case "P"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 35
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light yellow
Case "Q"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 19
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light blue
Case "R"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light green
Case "S"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 35
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light yellow
Case "T"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 19
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light blue
Case "U"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light green
Case "V"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 35
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light yellow
Case "W"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 19
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light blue
Case "X"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light green
Case "Y"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 35
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light yellow
Case "Z"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 19
Range(LColorCells).Interior.Pattern = xlSolid




'Default all other rows to no color
Case Else
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = xlNone

End Select

LRow = LRow + 1
Wend

Range("A2").Select[/VBA]

[mdmackillop]

1. Get rid of Select
2. Avoid repeated items
3. Limit to populated cell range

[vba]
Sub Cols()
Dim LRow As Integer
Dim LCell As String
Dim LColorCells As String
'Start at row 2
LRow = 2

'Update row colors for 1000 rows
While LRow <= Cells(Rows.Count, 2).End(xlUp).Row
LCell = "B" & LRow
'Color will be changed in columns A to AL
LColorCells = "A" & LRow & ":" & "AL" & LRow

Select Case Left(Range(LCell).Value, 1)

'Set row color to light yellow
Case "."
Range(LColorCells).Interior.ColorIndex = 19

'Set row color to light blue
Case 0 To 9
Range(LColorCells).Interior.ColorIndex = 34

'Set row color to light green
Case "A", "D", "G", "J", "M", "P", "S", "V", "Y"
Range(LColorCells).Interior.ColorIndex = 35

'Set row color to light yellow
Case "B", "E", "H", "K", "N", "Q", "R", "T", "W", "Z"
Range(LColorCells).Interior.ColorIndex = 19

'Set row color to light blue
Case "C", "F", "I", "L", "O", "R", "U", "X"
Range(LColorCells).Interior.ColorIndex = 34

'Default all other rows to no color
Case Else
Range(LColorCells).Interior.ColorIndex = xlNone

End Select
Range(LColorCells).Interior.Pattern = xlSolid

LRow = LRow + 1
Wend

Range("A2").Select
End Sub

[/vba]

You could also consider checking each cell in the range to see if it is coloured. If not then apply the colour.

[Dodgeitorels]

Once again ty very much mdmackillop