Conditional Formatting different color same criteria

[p9j123]

Hi,

I need help on setting a conditional formula. I need the cell to be colored differently once there is already a gap.
Each X represent a column, the first 5 column should be color Red, next 5 X is blue, next 5 X is green. Colors can be anything as long as they are different from each other.

X X X X X A X X X X X B X X X X X

 

[DanteAmor]

Yes Please, that should be fine.

Put the following code in the events of your sheet.
Change the range of A:Z columns by the columns you need

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, wr As Range, f As Long, n As Long, ini As Long, fin As Long
    
    Set r = Range([COLOR=#ff0000]"A:Z"[/COLOR])
    If Not Intersect(Target, r) Is Nothing Then
        If Target.Count > 100 Then Exit Sub
        For Each wr In Target.Rows
            f = wr.Row
            n = 1
            ini = 0
            Rows(f).Interior.ColorIndex = xlNone
            For j = 1 To Cells(f, Columns.Count).End(xlToLeft).Column + 1
                If Cells(f, j) = "x" Then
                    If ini = 0 Then ini = j
                Else
                    If ini > 0 Then
                        fin = j - 1
                        Select Case n
                            Case 1
                                Range(Cells(f, ini), Cells(f, fin)).Interior.ColorIndex = 3
                                n = n + 1
                            Case 2
                                Range(Cells(f, ini), Cells(f, fin)).Interior.ColorIndex = 5
                                n = n + 1
                            Case 3
                                Range(Cells(f, ini), Cells(f, fin)).Interior.ColorIndex = 4
                                n = 1
                        End Select
                        ini = 0
                    End If
                End If
            Next
        Next
    End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[p9j123]

Works like a charm, just one more question how can make the color random so that I am not limited to 3 colors?

 

[DanteAmor]

Works like a charm, just one more question how can make the color random so that I am not limited to 3 colors?

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, wr As Range, f As Long, n As Long, ini As Long, fin As Long
    
    Set r = Range("A:Z")
    If Not Intersect(Target, r) Is Nothing Then
        If Target.Count > 100 Then Exit Sub
        For Each wr In Target.Rows
            f = wr.Row
            n = 1
            ini = 0
            Rows(f).Interior.ColorIndex = xlNone
            For j = 1 To Cells(f, Columns.Count).End(xlToLeft).Column + 1
                If Cells(f, j) = "x" Then
                    If ini = 0 Then ini = j
                Else
                    If ini > 0 Then
                        fin = j - 1
                        Range(Cells(f, ini), Cells(f, fin)).Interior.ColorIndex = WorksheetFunction.RandBetween(3, 56)
                        ini = 0
                    End If
                End If
            Next
        Next
    End If
End Sub

 

[p9j123]

Thank you so much @DanteAmor this is exactly what I need.

 

[DanteAmor]

Thank you so much @DanteAmor this is exactly what I need.

I'm glad to help you. Thanks for the feedback.