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.

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" wrote in message ...
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" <deitbe @ consumer dot org wrote in message
...
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.

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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.

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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.

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" wrote in message ...
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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" <deitbe @ consumer dot org wrote in message
...
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.