Conditionally format Entire Row on Several Excel Worksheets 1

[IngDep]

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

 

[AnotherHiggins]

FYI: VBA questions should be asked in forum707.

I would recommend just turning on your macro recorder and then doing the Conditional Formatting you want. To get the entire column to be formatted according to the value in a single column, just use an absolute reference for the column in your fomula (ex: =[!]$[/!]A1 = "test")

Then wrap your code in a For Each. It'll look something like:

For Each Sht In Workbooks("WB_Name").Sheets
    Sht.Select
Next Sht

Post back to forum707 for any help cleaning up the recorded code or with the loop.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[IngDep]

Upon reviewing this again, I am interested in reviewing columns A through K on just four worksheets and highlighting, in blue font, those cells that exceed 4.99%

 

[Gavona]

As Skip said - just turn on the macro recorder while applying the conditional format to a single sheet. Then adapt his code for applying it to other sheets.

Personally I would put the 4.99% in a cell. And use Insert,Name,Define to name that cell TestCell.
Then the format condition in all worksheets would be:

.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=TestCell"

(OR maybe you really want to also highlight those cells that are less than -4.99%)

Gavin

 

[Gavona]

Apologies: As John said....

Gavin

 

[SkipVought]

Lets keep things straight! ;-)

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]