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 Im 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
dont 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 dont 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!

What do you mean by ... Since I cannot save Conditional Formatting rules in
Excel? Excel saves all the CF rules applied when you save the workbook.

--
__________________________________
HTH

Bob

"shairal" wrote in message
...
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!

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



"shairal" wrote:

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 Im 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
dont 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 dont 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!

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


"Patrick Molloy" wrote:

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



"shairal" wrote:

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 Im 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
dont 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 dont 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!

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

--
Rick (MVP - Excel)


"shairal" wrote in message
...
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 Im 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
dont 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 dont 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!