Conditional Formatting using VBA in Access

[Keith Wilby]

Reposted from another (old) thread with a similar title.

I have a spreadsheet that contains progress data in the range 0 to 1. The
data is populated from scratch from Access using VBA. I want cells to be
red if the number in the preceding column is lower, but I want to apply this
formatting from Access.

I've got an idea that the code will be some sort of loop but have no idea
what the syntax might be. Anyone done this or similar?

Just to clarify, what I want is to have the formatting set using VBA in
Access. I
need to be able to output my data to any Excel file so pre-formatting a
specific file isn't an option. Is this do-able?

Many thanks.

Keith.

 

[Bernie Deitrick]

Keith,

No need to loop if you use CF.

The syntax depends on which objects you've defined and set within code, and which range you are
using, and whether it is a fixed size or not....it would help to see your code, and to know the
range, but something along the lines of


'Set objRange = objExcel.Activeworbook.ActiveSheet.Range("C2:C15")
'Set objRange = objXLWkbk.ActiveSheet.Range("C2:C15")
'Set objRange = objXLWkSht.Range("C2:C15")

With objRange
.Select
.Cells(1, 1).Activate
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlGreater, _
Formula1:="=" & .Cells(1, 0).Address(False, False)
.FormatConditions(1).Interior.ColorIndex = 3
End With

HTH,
Bernie
MS Excel MVP

 

[Keith Wilby]

Keith,

No need to loop if you use CF.

The syntax depends on which objects you've defined and set within code,
and which range you are using, and whether it is a fixed size or not....it
would help to see your code, and to know the range, but something along
the lines of


'Set objRange = objExcel.Activeworbook.ActiveSheet.Range("C2:C15")
'Set objRange = objXLWkbk.ActiveSheet.Range("C2:C15")
'Set objRange = objXLWkSht.Range("C2:C15")

With objRange
.Select
.Cells(1, 1).Activate
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlGreater, _
Formula1:="=" & .Cells(1, 0).Address(False, False)
.FormatConditions(1).Interior.ColorIndex = 3
End With

HTH,
Bernie
MS Excel MVP

Absolutely spot on Bernie, many thanks indeed. Have a great day.

Keith.

 

[Keith Wilby]

Keith,

No need to loop if you use CF.

The syntax depends on which objects you've defined and set within code,
and which range you are using, and whether it is a fixed size or not....it
would help to see your code, and to know the range, but something along
the lines of


'Set objRange = objExcel.Activeworbook.ActiveSheet.Range("C2:C15")
'Set objRange = objXLWkbk.ActiveSheet.Range("C2:C15")
'Set objRange = objXLWkSht.Range("C2:C15")

With objRange
.Select
.Cells(1, 1).Activate
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlGreater, _
Formula1:="=" & .Cells(1, 0).Address(False, False)
.FormatConditions(1).Interior.ColorIndex = 3
End With

Hi Bernie, just one other thing ... I thought I'd be able to figure out how
to adapt your code such that the cell is one colour if "less than" and a
different colour if "more than" but I've failed miserably I'm afraid. Any
pointers?

Thanks again.

Keith.

 

[Keith Wilby]

Keith,

No need to loop if you use CF.

The syntax depends on which objects you've defined and set within code,
and which range you are using, and whether it is a fixed size or not....it
would help to see your code, and to know the range, but something along
the lines of


'Set objRange = objExcel.Activeworbook.ActiveSheet.Range("C2:C15")
'Set objRange = objXLWkbk.ActiveSheet.Range("C2:C15")
'Set objRange = objXLWkSht.Range("C2:C15")

With objRange
.Select
.Cells(1, 1).Activate
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlGreater, _
Formula1:="=" & .Cells(1, 0).Address(False, False)
.FormatConditions(1).Interior.ColorIndex = 3
End With

It's just occurred to me that what I need to do is colour all cells green
and then conditionally colour the others red by exception. Hopefully I can
work that one out. Many thanks for your patience ... sorry about the
multiple answers ;-)

Keith.

 

[Bernie Deitrick]

Keith,

To set the color, just use this as your first line

With objRange
.Interior.ColorIndex = 50 'or 4 or 35
......

HTH,
Bernie
MS Excel MVP

 

[Keith Wilby]

Keith,

To set the color, just use this as your first line

With objRange
.Interior.ColorIndex = 50 'or 4 or 35
.....

Thanks Bernie, works a treat.

Regards,
Keith.