If Then or Case help

[shairal]

I have a spreadsheet that is created once a week from an Access program. I
have recorded a macro that formats most of the data I’m looking to change,
however I need to do some conditional formatting. Since I cannot save
Conditional Formatting rules in Excel, I need to do this in a macro and I
don’t know enough about VBA to accomplish this on my own … help please!

The spreadsheet is column A:L however it varies in row count each week.
Colum D is a text column and sometimes includes the word ‘Customer’
somewhere in the cell – if it does, I want to highlight that cell in yellow
and give the entire row (A:L) a heavy border
Cells in column H, also a text column, are sometimes blank/empty – if a cell
blank, I want the entire row (A:L) highlighted in yellow with a heavy border

I have seen lots of If Then and Case examples, but I don’t know how to make
them select a dynamic range, or highlight a single cell if one condition is
met, or an entire row if another condition is met.

Any help you can provide is greatly appreciated!

 

[Patrick Molloy]

I understand that the sheet will be populated from Access, and you don't know
how to apply conditional formatting other than using VBA.

The easiest way to learn coding is to turn on the macro recorder and see hw
Excel does it. Having said that, its not always the prettiest piece of code.

Here's mine for you:

Sub SetCondFormat1()
With Columns("A:L")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR($D1=""Customer"",$H1="""")"
.FormatConditions(1).Interior.ColorIndex = 36
End With
End Sub

 

[Patrick Molloy]

I just noticed I missed off the heavy borders. There's an excercise for you

 

[Rick Rothstein]

Give this macro a try...

Sub HighlightCustomers()
Dim R As Range
Dim FirstAddress As String
ActiveSheet.UsedRange.ClearFormats
Set R = ActiveSheet.Columns("D").Find(What:="Customer", LookAt:=xlWhole)
If Not R Is Nothing Then
FirstAddress = R.Address
Do
R.Interior.ColorIndex = 6
Range(Cells(R.Row, "A"), Cells(R.Row, "L")). _
BorderAround Weight:=xlMedium
Set R = ActiveSheet.Columns("D").FindNext(R)
Loop While Not R Is Nothing And R.Address <> FirstAddress
End If
End Sub