Autoformat string based on values ​​in each cell using Excel VBA?

Question

I have a table 1

Column A has a date, for example. 5/30/2017

Column B has a status such as Success

Column C matters e.g. 500

Requirement: Apply custom conditional formatting in VBA when changing a cell

Let's say the change occurred in columns A, B or C in row 5

Regardless of whether a change has occurred in columns A, B or C, the same logic must be executed.

If the value of column A is less than Now (), then row 5 should be a red background and white text. Further checks should not be performed.

Else If column B is Success, then row 5 should be a green background and white text. Further checks should not be performed.

Else If column C is less than 500, then row 5 should be a blue background and white text. Further checks should not be performed.

Below is the VBA code for checking for a change in a cell - it automatically formats the cell in column b with a hyperlink.

Now I need to auto-format the entire line based on the above criteria.

Private Sub Worksheet_Change(ByVal Target As Range)

          If ((Not Intersect(Target, Range("B:B")) Is Nothing) Or (Not Intersect(Target, Range("F:F")) Is Nothing) Or (Not Intersect(Target, Range("G:G")) Is Nothing) Or (Not Intersect(Target, Range("I:I")) Is Nothing)) Then    

          End If

End Sub

Answer 1

Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range, R As Range
    Dim fCol As Long, bCol As Long

    Set Rng = Application.Intersect(Target, Columns("A:C"))

    If Not Rng Is Nothing Then

     Set Rng = Application.Intersect(Rng.EntireRow, Columns("A:C"))
     fCol = vbWhite

     For Each R In Rng.Rows

       If R.Cells(1, 1).Value <> vbNullString And R.Cells(1, 1).Value < Now Then
         bCol = vbRed
       ElseIf R.Cells(1, 2).Value <> vbNullString And R.Cells(1, 2).Value = "Success" Then
         bCol = vbGreen
       ElseIf R.Cells(1, 3).Value <> vbNullString And R.Cells(1, 3).Value < 500 Then
         bCol = vbBlue
       Else
         bCol = xlNone
         fCol = vbBlack
       End If

       R.EntireRow.Interior.Color = bCol
       R.EntireRow.Font.Color = fCol

     Next

    End If

End Sub

Edit

I have a table 1

Table1 ListObject ( Excel), , , , ( "A" "B" ..) , EntireRow:

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim LObj As ListObject
  Dim RngToWatch As Range
  Dim Rng As Range, R As Range
  Dim fCol As Long, bCol As Long

  Set LObj = ListObjects("Table1") ' the name of the table
  Set RngToWatch = Range(LObj.ListColumns(1).DataBodyRange, LObj.ListColumns(3).DataBodyRange)
  Set Rng = Application.Intersect(Target, RngToWatch)

  If Not Rng Is Nothing Then

    Set Rng = Application.Intersect(Target.EntireRow, RngToWatch)
    fCol = vbWhite

    For Each R In Rng.Rows

       If R.Cells(1, 1).Value <> vbNullString And R.Cells(1, 1).Value < Now Then
         bCol = vbRed
       ElseIf R.Cells(1, 2).Value <> vbNullString And R.Cells(1, 2).Value = "Success" Then
         bCol = vbGreen
       ElseIf R.Cells(1, 3).Value <> vbNullString And R.Cells(1, 3).Value < 500 Then
         bCol = vbBlue
       Else
         bCol = xlNone
         fCol = vbBlack
       End If

       With Application.Intersect(LObj.DataBodyRange, R.EntireRow)
          .Interior.Color = bCol
          .Font.Color = fCol
       End With

    Next

  End If

End Sub 

Answer 2

, ( ) Sheet1. , Sheet1 ( )

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim irow As Variant

  ' First identify the row changed
  irow = Target.Row

  ' Invoke row formatter routine
  Call DefineFormat(irow)

End Sub

( Sheet1, )

Sub DefineFormat(irow) ' Receive the row number for processing

    Dim vVal As Variant
    Dim Rng As Range
    Dim lFont, lFill As Long

    ' Define the basis for validation
    Dim Current, Success, limit As Variant ' Can be defined as constant as well
        Current = Date ' Set today date
        Success = "Success" ' Set success status check
        limit = 500 ' Set limit for value check

    ' Set range for the entire row - Columns A(index 1) to Column C (index 3)
    Set Rng = Range(Application.ActiveSheet.Cells(irow, 1).Address, Application.ActiveSheet.Cells(irow, 3).Address)
    lFont = vbWhite

    ' Assuming columns A, B and C needs to be formatted
    If Application.ActiveSheet.Cells(irow, 1) < Current Then
        lFill = vbRed  ' Check for col A
        Else:
            If Application.ActiveSheet.Cells(irow, 2) = Success Then
            lFill = vbGreen   ' Check for col B
            Else
                If Application.ActiveSheet.Cells(irow, 3) < limit Then
                 lFill = vbBlue   ' Check for col C
                 Else     ' Default formatting
                    lFill = xlNone
                    lFont = vbBlack
                End If
            End If
    End If

        Rng.Interior.Color = lFill
        Rng.Font.Color = lFont
End Sub

( , )

, , DefineFormat , .