VBA to change color of cells information is being pasted into

[jondavis1987]

I have this vba for a command button. Cell D2 has a dropdown list that has the words "High", "Medium", "Low". The button will copy cells A2:C2 and paste the values in the last row. If D2 says High I'd like A2:C2 to be pasted into cells that are green, medium into yellow, and low into red. Any advice on how to alter this?

Sub Log_Changes()

    Dim lastRow As Long
    Dim ws     As String
    Dim srcName As String
    Dim wsName As String
    ws = ActiveSheet.Name
    Application.DisplayAlerts = False
    
    lastRow = Sheets(ws).Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    
    Sheets(ws).Range("A2:C2").Copy
    Sheets(ws).Range("A" & lastRow).PasteSpecial xlPasteValues

     
End Sub

 

[jdellasala]

Have you considered Conditional Formatting? This is a long video, but this channel has the best explanation of Conditional Formatting. If you want shorter videos, here's the Search of the channel's shorter videos.

 

[Fluff]

How is CF meant to copy/paste data?

 

[jondavis1987]

Have you considered Conditional Formatting? This is a long video, but this channel has the best explanation of Conditional Formatting. If you want shorter videos, here's the Search of the channel's shorter videos.

Can't really get conditional formatting to do what I need it to do.

 

[jondavis1987]

Figured it out

Sub Log_Changes()

    Dim lastRow As Long
    Dim ws     As String

    wsName = ActiveSheet.Range("A1")
    ws = ActiveSheet.Name
    Application.DisplayAlerts = False

    
    lastRow = Sheets(ws).Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    If Sheets(ws).Range("D5") = "HIGH" Then Sheets(ws).Range("A" & lastRow).Interior.Color = vbGreen
    If Sheets(ws).Range("D5") = "MEDIUM" Then Sheets(ws).Range("A" & lastRow).Interior.Color = vbYellow
    If Sheets(ws).Range("D5") = "LOW" Then Sheets(ws).Range("A" & lastRow).Interior.Color = vbRed
    If Sheets(ws).Range("D5") = "HIGH" Then Sheets(ws).Range("B" & lastRow).Interior.Color = vbGreen
    If Sheets(ws).Range("D5") = "MEDIUM" Then Sheets(ws).Range("B" & lastRow).Interior.Color = vbYellow
    If Sheets(ws).Range("D5") = "LOW" Then Sheets(ws).Range("B" & lastRow).Interior.Color = vbRed
    If Sheets(ws).Range("D5") = "HIGH" Then Sheets(ws).Range("C" & lastRow).Interior.Color = vbGreen
    If Sheets(ws).Range("D5") = "MEDIUM" Then Sheets(ws).Range("C" & lastRow).Interior.Color = vbYellow
    If Sheets(ws).Range("D5") = "LOW" Then Sheets(ws).Range("C" & lastRow).Interior.Color = vbRed
    If Sheets(ws).Range("D5") = "HIGH" Then Sheets(ws).Range("D" & lastRow).Interior.Color = vbGreen
    If Sheets(ws).Range("D5") = "MEDIUM" Then Sheets(ws).Range("D" & lastRow).Interior.Color = vbYellow
    If Sheets(ws).Range("D5") = "LOW" Then Sheets(ws).Range("D" & lastRow).Interior.Color = vbRed
    Sheets(ws).Range("A5:D5").Copy
    Sheets(ws).Range("A" & lastRow).PasteSpecial xlPasteValues
   
End Sub