Using the following vba code that I obtained from another site to conditionally format an entire row on 4 Excel worksheets - Sheet 1, Sheet 2, Sheet 3, and Sheet 4 if there is a value > 5.0% in one of the columns.
Currently, the code is set up to just search one column - column J on worksheet named "Data."
Each worksheet has approximately 15 columns.
What modifications are needed?
Still learning vba code and appreciate any insight.
Thank you.
Sub Format_By_Row()
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("Data")
'Select the sheet so we can change the window view
.Select
'Go to normal view for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
'Turn off Page Breaks, again for speed
.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 values in the J column in this example
'[Note: alternatively, if whole row is named,
'could use that name if it makes things clearer:
'e.g. With .Cells(Lrow, "First_Name")
'Also With .Cells(Lrow, 10) works, too, if row/column math desired.
With .Cells(Lrow, "J")
'Safety first
If Not IsError(.Value) Then
'Do tests and set BG colors appropriately
'ORIGINAL BLOCK OF CODE BELOW
'Select Case .Value
' Case "Alice"
' Rows(Lrow).Interior.Color = RGB(255, 0, 0)
' Case "Bob"
' Rows(Lrow).Interior.Color = RGB(255, 153, 0)
'End Select
Select Case .Value
Case Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="0.049"
Rows(Lrow).Interior.Color = RGB(255, 0, 0)
Case Case Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLesser, Formula1:="0.049"
Rows(Lrow).Interior.Color = RGB(255, 153, 0)
End Select
End If
End With
Next Lrow
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
Currently, the code is set up to just search one column - column J on worksheet named "Data."
Each worksheet has approximately 15 columns.
What modifications are needed?
Still learning vba code and appreciate any insight.
Thank you.
Sub Format_By_Row()
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("Data")
'Select the sheet so we can change the window view
.Select
'Go to normal view for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
'Turn off Page Breaks, again for speed
.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 values in the J column in this example
'[Note: alternatively, if whole row is named,
'could use that name if it makes things clearer:
'e.g. With .Cells(Lrow, "First_Name")
'Also With .Cells(Lrow, 10) works, too, if row/column math desired.
With .Cells(Lrow, "J")
'Safety first
If Not IsError(.Value) Then
'Do tests and set BG colors appropriately
'ORIGINAL BLOCK OF CODE BELOW
'Select Case .Value
' Case "Alice"
' Rows(Lrow).Interior.Color = RGB(255, 0, 0)
' Case "Bob"
' Rows(Lrow).Interior.Color = RGB(255, 153, 0)
'End Select
Select Case .Value
Case Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="0.049"
Rows(Lrow).Interior.Color = RGB(255, 0, 0)
Case Case Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLesser, Formula1:="0.049"
Rows(Lrow).Interior.Color = RGB(255, 153, 0)
End Select
End If
End With
Next Lrow
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub