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" <(E-Mail Removed)> wrote in message news:4688d1aa$(E-Mail Removed)...
> 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
news:(E-Mail Removed)...
> 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
news:(E-Mail Removed)...
> 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
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news:4688f5ed$(E-Mail Removed)...
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:(E-Mail Removed)...
>> 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
news:(E-Mail Removed)...
> 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.