How to change color tones used in VBA code / macro result (red, green)

Question

I use the following VBA code to change the color of the rows in my table every time the value in column A changes (so that all records with the same value in column A are grouped by color. The spreadsheet is sorted by column A so that the elements are already grouped, I just needed their color).

In any case, when I run this macro, the lines are colored red and green (which are very bright and overwhelming colors for this purpose). I need something more subtle.

How can I change this? Or can I specify in my VBA code to use specific colors at the rgb or color index? {I am using Excel 2007}

Sub colorize() Dim r As Long, val As Long, c As Long r = 1 val = ActiveSheet.Cells(r, 1).Value c = 4 For r = 1 To ActiveSheet.Rows.Count If IsEmpty(ActiveSheet.Cells(r, 1).Value) Then Exit For End If If ActiveSheet.Cells(r, 1).Value <> val Then If c = 3 Then c = 4 Else c = 3 End If End If ActiveSheet.Rows(r).Select With Selection.Interior .ColorIndex = c .Pattern = xlSolid End With val = ActiveSheet.Cells(r, 1).Value Next End Sub 

Answer 1

It turns out that all I had to do was change a few digits in the code that I posted in my question. I am a bold number that I had to change. These numbers correspond to the color identifier (for example, what Belisarious put). NOTE. I had to put apostrophes so that the VBA code was not recognized as VBA code (because if it would not be in bold). See Source Question for the correct code.

Dim r As Long, val As Long, c As Long

'r = 1
'val = ActiveSheet.Cells (r, 1). Value
'c = 4

'For r = 1 For ActiveSheet.Rows.Count
If IsEmpty (ActiveSheet.Cells (r, 1) .Value) Then
Output for
End if

'If ActiveSheet.Cells (r, 1) .Value <> val Then
If c = 3 Then c = 4
Else
c = 3
End if
End if

 ActiveSheet.Rows(r).Select With Selection.Interior .ColorIndex = c .Pattern = xlSolid End With val = ActiveSheet.Cells(r, 1).Value 

Further

End Sub

Answer 2

Run this program (here loans)

 Sub colors56() '57 colors, 0 to 56 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'pre XL97 xlManual Dim i As Long Dim str0 As String, str As String For i = 0 To 56 Cells(i + 1, 1).Interior.ColorIndex = i Cells(i + 1, 1).Value = "[Color " & i & "]" Cells(i + 1, 2).Font.ColorIndex = i Cells(i + 1, 2).Value = "[Color " & i & "]" str0 = Right("000000" & Hex(Cells(i + 1, 1).Interior.Color), 6) 'Excel shows nibbles in reverse order so make it as RGB str = Right(str0, 2) & Mid(str0, 3, 2) & Left(str0, 2) 'generating 2 columns in the HTML table Cells(i + 1, 3) = "#" & str & "#" & str & "" Cells(i + 1, 4).Formula = "=Hex2dec(""" & Right(str0, 2) & """)" Cells(i + 1, 5).Formula = "=Hex2dec(""" & Mid(str0, 3, 2) & """)" Cells(i + 1, 6).Formula = "=Hex2dec(""" & Left(str0, 2) & """)" Cells(i + 1, 7) = "[Color " & i & ")" Next i done: Application.Calculation = xlCalculationAutomatic 'pre XL97 xlAutomatic Application.ScreenUpdating = True End Sub 

Output Sample:

Answer 3

You can customize the color palette by code, I think the page here will answer your question: http://www.databison.com/index.php/excel-color-palette-and-color-index-change-using-vba /

 Sub change_palette_color dim color_index as long color_index = 10 ActiveWorkbook.Colors(color_index) = RGB(128, 128, 128) End sub