Conditional Formatting

[dmkennard2]

Hi,
I have code that exports data to a spreadsheet.
What i would like to do is then apply conditional formatting to each row based on a value in a column in that row.

I am having problems getting the code to work.
This is what i have so far:

-----------------------------------------
Dim xlApp As Excel.Application
Dim xlFormat As Excel.Range
Dim xlCell As Excel.Range
Dim xlsheet As Excel.Worksheet
Dim xlBook As Excel.Workbook
Dim MyDate, MyStr
Dim strPath As String
Dim conStart As Range
Dim conFinish As Range
Dim conCell As Range


MyDate = Date
MyStr = Format(MyDate, "dd-mmm-yyyy")

filein = ("\\test\test " & MyStr & ".xls")
sheetin = ("qrytest")

Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open(filein)
Set xlsheet = xlBook.Sheets(sheetin)

Set xlFormat = xlsheet.Range("A272")
Set xlCell = xlsheet.Range("K51")

With xlFormat
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=$K$51" = "Charge"""
.FormatConditions(1).Interior.ColorIndex = 36
End With

When i run this code, it only colours the blank cells.

Dazz

 

[PHV]

You meant this ?
Formula1:="=$K$51=""Charge"""

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dmkennard2]

Ok, but this still does not answer the question.
Can i format each row bassed on a value in a cell in that row using VBA?

Dazz

 

[PHV]

Have you tried to play with the code generated by the excel macro recorder ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dmkennard2]

Thanks fella, done it.

New code:

With xlRange
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$K1=""Charge"""
.FormatConditions(1).Interior.ColorIndex = 36
End With

Thanks for pointing me in the right direction.

Dazz