Theme color accent to RGB

[Afro_Cookie]

I have some code that helps me highlight unique cells as yellow using VBA. I did this to eliminate my conditional formatting duplicating itself and causing issues in my worksheets. The issue is I want to use RGB colours instead of Themeaccentcolours

Can some of you more experienced people please help me modify that specific part of my code so I can use RGB(0,255,0) in place of xlThemeColorAccent2

Option Explicit

Public Sub markunique()
Dim iColour As Integer
Dim rng As Range
Dim rngCell As Variant
Dim LR As Long
Dim vVal

LR = Cells(Rows.Count, "B").End(xlUp).Row

Set rng = Range("B6:B" & LR)
iColour = xlThemeColorAccent2

rng.FormatConditions.Delete

For Each rngCell In rng.Cells
    vVal = rngCell.Text
    If (WorksheetFunction.CountIf(Range("B6:B" & rngCell.Row), vVal) = 1) Then
        rngCell.Interior.ColorIndex = iColour
    Else
        rngCell.Interior.Pattern = xlNone
    End If
    
Next
End Sub

 

[Jimmypop]

Hi there

Maybe try..

Option Explicit

Public Sub markunique()
Dim iColour As Long
Dim rng As Range
Dim rngCell As Variant
Dim LR As Long
Dim vVal

LR = Cells(Rows.Count, "B").End(xlUp).Row

Set rng = Range("B6:B" & LR)
iColour = RGB(0, 255, 0)

rng.FormatConditions.Delete

For Each rngCell In rng.Cells
    vVal = rngCell.Text
    If (WorksheetFunction.CountIf(Range("B6:B" & rngCell.Row), vVal) = 1) Then
        rngCell.Interior.Color = iColour
    Else
        rngCell.Interior.Pattern = xlNone
    End If
    
Next
End Sub

 

[Afro_Cookie]

Hi there

Maybe try..

Option Explicit

Public Sub markunique()
Dim iColour As Long
Dim rng As Range
Dim rngCell As Variant
Dim LR As Long
Dim vVal

LR = Cells(Rows.Count, "B").End(xlUp).Row

Set rng = Range("B6:B" & LR)
iColour = RGB(0, 255, 0)

rng.FormatConditions.Delete

For Each rngCell In rng.Cells
    vVal = rngCell.Text
    If (WorksheetFunction.CountIf(Range("B6:B" & rngCell.Row), vVal) = 1) Then
        rngCell.Interior.Color = iColour
    Else
        rngCell.Interior.Pattern = xlNone
    End If
   
Next
End Sub

An Error is generated and the below line of code is highlighted.

Run-time error '9':
Subscript out of range

rngCell.Interior.ColorIndex = iColour

 

[Jimmypop]

An Error is generated and the below line of code is highlighted.

Run-time error '9':
Subscript out of range

rngCell.Interior.ColorIndex = iColour

Did you copy the code exactly as provided?

That line has been updated to

rngCell.Interior.Color = iColour

in the code provided...

 

[Afro_Cookie]

Did you copy the code exactly as provided?

That line has been updated to

rngCell.Interior.Color = iColour

in the code provided...

i didn't. sorry. I missed that modification.

I've modified that line and now it works perfectly. Thank you for the quick and supportive responses.

 

[Jimmypop]

i didn't. sorry. I missed that modification.

I've modified that line and now it works perfectly. Thank you for the quick and supportive responses.

No problem... glad we could help and thanks for the feedback