color the previous cell if the next cell has the value-CodePudding

I want to color the previous cell if the next cell has the VALUE "F".

Data must be in the one row, that's why I got a problem with that.

Conditional formating wont work in here maybe some macro. Does anyone know hot to solve that? Thank you in advance

CodePudding user response：

Conditional formatting worked for me. A basic example is shown below ...

CodePudding user response：

Please, copy the next code event in the respective sheet code module (right click on the sheet name and choose View Code):

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lastR As Long, rng As Range
    lastR = Me.Range("H" & Me.rows.count).End(xlUp).row
    Set rng = Me.Range("H2:BL" & lastR) 'use here the columns range you need to process.
    If Not Intersect(Target, rng) Is Nothing Then
        If Target.Value = "F" Then
            Target.Offset(, -1).Interior.Color = vbGreen
        Else
            Target.Offset(, -1).Interior.Color = xlNone
        End If
    End If
End Sub