Conditional Format Using Other Worksheet Reference

[shelby55]

Hello

I am using Excel 2003. I have a workbook with many worksheets. In one worksheet I want to conditional format the colour of a cell based on the results in another worksheet.

I tried to use the conditional format feature but it doesn't allow cell reference to other worksheets thus the need to use VBA.

This is what I have so far but it's not working:

[C18].CurrentRegion.Select

With Selection
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OR(ISTEXT(Summary!R[22]C[-3]),Summary!R[22]C[-3]<Summary!R[25]C[-3]*0.75)"
    .FormatConditions(1).Interior.ColorIndex = 3
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(Summary!R[22]C[-3]<Summary!R[25]C[-3],Summary!R[22]C[-3]>=Summary!R[25]C[-3]*0.75)"
    .FormatConditions(2).Interior.ColorIndex = 6
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
        , Formula1:="=""Summary!R[25]C[-3]"""
    .FormatConditions(3).Interior.ColorIndex = 10
End With

I've placed the above in the sheet where cell C18 resides. Please advise what I'm doing wrong - thanks.

 

[SkipVought]

hi,

it doesn't allow cell reference to other worksheets thus the need to use VBA.

You cannot do in VBA what you cannot do in Excel! CF will NOT work with references external to the sheet.

CF references to ranges external to the sheet CAN be done, using Named Ranges, however. External workbooks CANNOT be so referenced in CF.

I woud suggest using MS Query to return data from your external workbook, name the ranges in the table and then CF on that reference. None or very little VBA requied.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Thanks for the reply. Sorry but it's not an external workbook, it's a different worksheet in the same workbook. Does your response still apply?

If same worksheet, what are my options for doing this? Thanks.

 

[PHV]

it's a different worksheet in the same workbook
So, use named range(s)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Thanks PHV...but what is the syntax compared to what I have?

 

[SkipVought]

As I explained, external sheets can be used using Named Ranges!

NO VBA REQUIRED!

In the CF window, reference the Named Range like...
[tt]
=Match(a2,YourNamedRange,0)>0
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}