change certain cell colours in VBA

K

Klami

Joined
Feb 9, 2015
Messages
2
Reaction score
0
Hi to all, KLAMI, a newbie to VBA here, asking for help.
I have a piece of code that currently colors an entire row dependent on the last number digit in a certain column. It then loops and does the same for the entire worksheet if required making rows of the various colors stipulated
Can someone please advise how I can change this to let it to continue to do the same but ignore non adjacent cells that may contain any one of 4 different colors in the same row, i.e. Black, Brown, grey and white?

I would also appreciate if someone could help me speed up the existing loop, if the problem above can not be resolved?

Here is the code that I use:

Sub ColourRow()
'
' Private Sub Worksheet_Change(ByVal Target As Range)
'
' CoulorIt Macro
'
' Dim rng As Range
Dim i As Long
Application.ScreenUpdating = False
Set rng = Range("B1:B5")
i = 2
Do
With rng
If Cells(i, 6).Value = "1" Then
Cells(i, 6).EntireRow.Interior.ColorIndex = 3
ElseIf Cells(i, 6).Value = "2" Then
Cells(i, 6).EntireRow.Interior.ColorIndex = 6
ElseIf Cells(i, 6).Value = "3" Then
Cells(i, 6).EntireRow.Interior.ColorIndex = 10
ElseIf Cells(i, 6).Value = "4" Then
Cells(i, 6).EntireRow.Interior.ColorIndex = 33
ElseIf Cells(i, 6).Value = "5" Then
Cells(i, 6).EntireRow.Interior.ColorIndex = 29
ElseIf Cells(i, 6).Value = "6" Then
Cells(i, 6).EntireRow.Interior.ColorIndex = 45
ElseIf Cells(i, 6).Value = "7" Then
Cells(i, 6).EntireRow.Interior.ColorIndex = 23
ElseIf Cells(i, 6).Value = "8" Then
Cells(i, 6).EntireRow.Interior.ColorIndex = 43
ElseIf Cells(i, 6).Value = "9" Then
Cells(i, 6).EntireRow.Interior.ColorIndex = 17
ElseIf Cells(i, 6).Value = "0" Then
Cells(i, 6).EntireRow.Interior.ColorIndex = 42
Else
Cells(i, 6).EntireRow.Interior.ColorIndex = 2
End If
End With
i = i + 1
Loop Until i = 100

Application.ScreenUpdating = True

End Sub

Thanks to any respondants
 
Hi Klami,
I think I can help you if you help me understand what you are trying to do.

Your macro colors rows based on the number in the cell, not the last digit. Do you want the code to find the last digit? The formula is right(target, character).

I am not sure what you mean by non adjacent cells. As the code runs to through do you want it to skip a row if the row above it has a color?

I don't know the use of the 'mg' variable. Are you expecting some condition in the range to impact the code?

Is row 100 significant or did you put it in the code to stop the macro? Would you like it to stop at the last entered value in column F? That may save some time if you only have 20 rows- it would stop rather than keep going until 100.

If you take a few minutes and add more about what you are trying to accomplish, I think we can get you the code to do it.

Stoneboysteve
 
Thanks Stoneboysteve, in answer to your points/queries
The cell (i, 6) contains the last digit of another cell containing numbers via "=RIGHT(F2,1) " and is used to determine the color needed

By non adjacent cells, i mean that cells H8 to L8, P8 to H8 and U8 to Y8 may contain colors white, brown and black respectively that I want to keep after recoloring the rest of the row.

I don't know of the "mg" variable either as it was in a piece of code that someone else has helped me with. I thought it was actualy a "r n g" variable to do with "range" but having removed it and all references to it, the code still appears to do as I need, except for the mods that I am looking for.

Row 100 is just used to stop the loop. after 100 row, while I have been testing the code. Eventually, I will need it to run to row 41000 approx but while testing, I found the loop takes over a minute to complete, hence the 100 to cut down the testing time and, hence the request for inf on a faster way of running the macro.

As I said, I am completely new to VBA and although I can perceive some of the logic in the statements, I have no idea of the sytax needed to produce any code that works well.

Hope this clarifies
Klami
 
Back
Top