VBA change cell colour

[kitsa]

Hi,
I have created VBA to action my spreadsheet, but I'm lost on how to change a cell colour e.g. cell "O35" to be same colour as cell "N35". It keeps telling error, can anyone help me finish this coding.
Sorry but I don't know how to upload the spreadsheet for you to play with.

Private Sub Rollovertest_Click()
Application.DisplayAlerts = False
    Application.ScreenUpdating = False
   Dim i As Long
   Dim Lastrow As Long
Lastrow = Sheet2.Cells(Rows.Count, "B").End(xlUp).Row
    With Sheet2
        For i = 1 To Lastrow
            Select Case .Cells(i, 3).Value
                Case "S", "V": .Cells(i, "AB").Copy: .Cells(i, "AC").PasteSpecial xlPasteValues
            End Select
        Next
    End With
      
   Dim vR As Long, vN As Long
    With Sheet2
        vR = .Columns(1).Find("*", , , , , xlPrevious).Row
        For vN = vR To 2 Step -1
            If .Cells(vN, "O").Value = Range("AH1") Then
                vN2 = vN + 1
               .Rows(vN2).Insert
                .Range(Cells(vN, 1), Cells(vN, "AG")).Copy .Range("A" & vN2)
                .Cells(vN2, "F") = Cells(vN, "F") + 0.01
                .Range("AI1:AJ1").Copy Range(.Cells(vN2, "N"), Cells(vN2, "O"))
                .Range(.Cells(vN2, "T"), Cells(vN2, "V")) = ""
                 With .Range(.Cells(vN2, "T"), Cells(vN2, "V")).Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent2
                    .TintAndShade = 0.599993896298105
                    .PatternTintAndShade = 0
                 End With
                .Range("AC" & vN2) = 0
                .Range("AC" & vN2).NumberFormat = _
                    "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
            End If
        Next vN
    End With
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

 

[kitsa]

Try this one

Test Rollover 06.11.23.xlsm

Microsoft Excel Workbook

1drv.ms

 

[kevin9999]

That worked

 

[kevin9999]

OK, things are a bit clearer now. Couple of things wrong with your code v. the file you shared:
1. Your code refers to Sheet2 - there is only one sheet in your attachment
2. This line:

If .Cells(vN, "O").Value = Range("AH1") Then

is comparing a $ amount in column O with a date in AH1. They'll never match (be true) therefore nothing will happen thereafter
It's probably best, now I have your actual file(?) that you explain in plain English exactly what you want to occur when the macro runs.

 

[kitsa]

Hi Kevin,
I've changed it now as per new share link. so basically want I want it to do is once VBA has run, I need e,g, column "O" which has same date as cell AH1 to change same colour as cell AH1 or Column "N", as per image.

Test Rollover 08.11.23.xlsm

Microsoft Excel Workbook

1drv.ms

This is the code I have in the sheet:

Private Sub Rollovertest_Click()
Application.ScreenUpdating = False
    Dim i As Long, Lastrow As Long, vN2 As Long, ClrIndx As Double
    Lastrow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
    With Sheet1
        For i = 1 To Lastrow
            Select Case .Cells(i, 3).Value
                Case "S", "V"
                .Cells(i, "AB").Copy
                .Cells(i, "AC").PasteSpecial xlPasteValues
                Application.CutCopyMode = False
            End Select
        Next
        Lastrow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
        For i = Lastrow To 2 Step -1
            If .Cells(i, "O").Value = Range("AH1") And .Cells(i, "A").Value = "V" Then
                vN2 = i + 1
                .Rows(vN2).Insert
                .Range(Cells(i, "A"), Cells(i, "AG")).Copy .Range("A" & vN2)
                .Cells(vN2, "F") = Cells(i, "F") + 0.01
                .Range("AI1:AJ1").Copy Range(.Cells(vN2, "N"), Cells(vN2, "O"))
                .Range(.Cells(vN2, "T"), Cells(vN2, "V")) = ""
                With .Range(.Cells(vN2, "T"), Cells(vN2, "V")).Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent2
                    .TintAndShade = 0.599993896298105
                    .PatternTintAndShade = 0
                End With
                .Range("AC" & vN2) = 0
                .Range("AC" & vN2).NumberFormat = _
                "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                ClrIndx = .Range("N" & i).Interior.ColorIndex
                          .Range("O" & vN2).Interior.ColorIndex = ClrIndx
             End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

 

[kevin9999]

If that's the only thing you're missing from your current method/code (you make no mention of the copy/paste/insert in your code so I assume that's doing what you want), then why not keep it simple & use conditional formatting. Here's a small section to demonstrate:

Test Rollover 08.11.23.xlsm
	N	O
70
71
72
73	21/01/22	21/01/22
74	22/01/22	30/04/22
75	1/05/22	31/05/22
76	1/06/22	30/06/22
77
78
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O3:O104	Expression	=O3=$AH$1	text	NO

 

[kitsa]

If that's the only thing you're missing from your current method/code (you make no mention of the copy/paste/insert in your code so I assume that's doing what you want), then why not keep it simple & use conditional formatting. Here's a small section to demonstrate:

Test Rollover 08.11.23.xlsm
	N	O
70
71
72
73	21/01/22	21/01/22
74	22/01/22	30/04/22
75	1/05/22	31/05/22
76	1/06/22	30/06/22
77
78
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O3:O104	Expression	=O3=$AH$1	text	NO

I guess that could work, but don't want the colour to change until the VBA is finalised?

 

[kitsa]

I guess that could work, but don't want the colour to change until the VBA is finalised?

Hi Kevin,
I have applied the condition formatting, as per code, but the problem I have is once I change cell AH1 to different date, the colour changes back to yellow. This is what I was worried about using conditioning formatting. Is there anything I can do to resolve this?

Columns("O:O").Select
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=O1=$AH$1"
         Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.599963377788629
    End With
      Selection.FormatConditions(1).StopIfTrue = NO
        Range("AE3").Select

 

[kevin9999]

If you want the colour change to be permanent, then CF isn't going to be the solution. Going back to your original post, on which line of code were you getting the error?

It keeps telling error