Amedning VB to incorporate Cell range as well as Row Count

[Stefone]

Hi

I was wondering if someone can help me.

I have a conditional formatting VB running on my spreadsheet that I need help amending and I am lost.

I can master basic but I now need to alter this to include a bit extra.

Currently I have data on a worksheet that is amended daily. I have a status box (Column K). This is also that is associated to a range labelled CurrentStatus that isn't brought into the VB. Currently the conditionally formatting kicks in whenever status is changed and formats its accordingly.

I want to be able to now take current formatting and only highlight cells required on each row, which is column A to column O.

I also wonder if it is possible and how to add further lines to say if cell value is change to blank then remove conditional formatting (row should now be No Fill)

I hope someone can help

See below

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With Sheets("DDs To Reinstate")
'Select the sheet to change the window view
.Select
'Go to normal view
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
'Turn off Page Breaks
.DisplayPageBreaks = False
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'Loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1
'Check the text in the column K
'example With .Cells(Lrow, "Reinstated")
With .Cells(Lrow, "K")

'Safety first
If Not IsEmpty(.Value) Then

'Sets Background Colour dependant upon Value in Column J
Select Case .Value
Case "Re-Instated"
Rows(Lrow).Interior.Color = RGB(0, 255, 0)
Case "COT"
Rows(Lrow).Interior.Color = RGB(255, 102, 0)
Case "Incorrect Contact Details"
Rows(Lrow).Interior.Color = RGB(255, 0, 0)
Case "Will Not Re-Instate"
Rows(Lrow).Interior.Color = RGB(255, 255, 0)
Case "Problem"
Rows(Lrow).Interior.Color = RGB(0, 0, 255)

End Select
End If
End With
Next Lrow
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

 

[VoG]

Hello and welcome to MrExcel.

Does this work as expected?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
    CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With
With Sheets("DDs To Reinstate")
    'Select the sheet to change the window view
    .Select
    'Go to normal view
    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView
    'Turn off Page Breaks
    .DisplayPageBreaks = False
    'Set the first and last row to loop through
    Firstrow = .UsedRange.Cells(1).Row
    Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    'Loop from Lastrow to Firstrow (bottom to top)
    For Lrow = Lastrow To Firstrow Step -1
    'Check the text in the column K
    'example With .Cells(Lrow, "Reinstated")
    With .Cells(Lrow, "K")
        'Sets Background Colour dependant upon Value in Column K
        Select Case .Value
            Case "Re-Instated": Range("A" & Lrow & ":O" & Lrow).Interior.Color = RGB(0, 255, 0)
            Case "COT": Range("A" & Lrow & ":O" & Lrow).Interior.Color = RGB(255, 102, 0)
            Case "Incorrect Contact Details": Range("A" & Lrow & ":O" & Lrow).Interior.Color = RGB(255, 0, 0)
            Case "Will Not Re-Instate": Range("A" & Lrow & ":O" & Lrow).Interior.Color = RGB(255, 255, 0)
            Case "Problem": Range("A" & Lrow & ":O" & Lrow).Interior.Color = RGB(0, 0, 255)
            Case "": Range("A" & Lrow & ":O" & Lrow).Interior.ColorIndex = xlNone
        End Select
    End With
    Next Lrow
    End With
ActiveWindow.View = ViewMode
With Application
    .ScreenUpdating = True
    .Calculation = CalcMode
End With
End Sub