Excel VBA Apply Conditional Formatting to a Row
Thank you for looking at my question,
I use the vba code below to grab data from an Excel workbook.
The number of rows will vary but there will always only ever be four columns.
For each row I would like to cell background colour in columns 1,2,3 and 4 to turn red if the cell value in cell 4 is greater than 6.
How can I achieve this please?
I use the vba code below to grab data from an Excel workbook.
The number of rows will vary but there will always only ever be four columns.
For each row I would like to cell background colour in columns 1,2,3 and 4 to turn red if the cell value in cell 4 is greater than 6.
How can I achieve this please?
Sub Get_Data()
Dim wbk As Workbook
Dim destWbk As Workbook
Set destWbk = ActiveWorkbook
FileToOpen = "K:\Working Data\Loading.xls"
Set wbk = Workbooks.Open(FileToOpen)
wbk.Sheets(1).Range("A1:IV65536").Copy Destination:=destWbk.Sheets("TotalReq_vs_Bars").Range("A1")
wbk.Close
End Sub
Rory Archibald
Do you mean you want columns A:D to turn red if the value in column D of that row is >6?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
> rorya,
Sorry, that's exactly what I mean
Sorry, that's exactly what I mean
Hello,
What version of Excel? Based on the .xls extension, I assume you're using 2003 or earlier. In that case something along the lines of
What version of Excel? Based on the .xls extension, I assume you're using 2003 or earlier. In that case something along the lines of
destWbk.Range("D1").Select
With destWbk.Columns("A:D")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$D1>6"
.FormatConditions(1).Interior.ColorIndex = 3
End With
Sorry, StephenJR, slow fingers ...
Sorry, don't think that will work, will have to come back if needed.
ASKER
Excellent, Thank you
Range("A1:D1").Select ' Sel 4 Cols 1st Row
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$D1>6" ' Col 4 > 6
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255 ' Red Bkgnd
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.Copy
Range("A2:D65536").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False ' Copy Formats to other rows
Application.CutCopyMode = False