Emulate Conditional Formating in Excel 2003 3

[thefear]

Hi Folks,

I hope someone can help, this has had me going in circles since Friday.

I'm trying to emulate the conditional formatting of Excel 2007 in 2003.

I've been given a workbook that another has created. At the top of its only worksheet is a small table, (C4:C8), that holds 5 possible values. Each cell is a different colour. There is a column (D4x). The cells in this column use validation to restrict entry to the contents of the small table.

I need the background colour of the selected cell to be set to the colour of the matching cell in the small table. I've used Match, but that only gives me the relative position in the table. i.e. the first option is in C4, so I want it to return 4, but it returns 1. I think what I'm asking is how to use the Offset function in this regard, but so far my attempts haven't borne fruit.

Thanks in advance

 

[GlennUK]

Surely what you want is the relative position in the table. You can then reference the appropriate cell, and apply the same interior to the cell in column D.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[thefear]

Is that what I need? In the example above, the first entry in the table is in cell C4 but my function, (worksheet_Change)...

Application.WorksheetFunction.Match(Target.Value, Worksheets("Commercial").Range("Status_Options"), False)

...returns 1. I was hoping to concatenate the returned value with 'C' to give me the cell reference, but in this case I can't do so without adding a magic 3, which I don't really want to do. It feels a bit like being defeated

 

[combo]

Instead of Match you could use:
[tt]Worksheets("Commercial").Range("Status_Options").Find[/tt]
that directly returns a range (or error if no match).

combo

 

[thefear]

Hi Combo. Thanks for looking!

Where do I put what I'm looking for in that string?

 

[SkipVought]

Worksheets("Commercial").Range("Status_Options").Find
that directly returns a range (or error if no match).

To the range object that is assigned, returns an object reference or NOTHING.

So typically...

if Worksheets(SomeSheet).Range(SomeRange).Find(somelookup)
 is nothing then
   'nothing was found
else
   'somelookup was found
end if

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[thefear]

Thanks Skip, but I'm still having problems. I've put your line of code into the following...

Dim cellref As Range

cellref = Worksheets("Commercial").Range("Status_Options").Find(Target)

If cellref Is Nothing Then

Else

Target.Interior.Color = Worksheets("Commercial").Range(cellref).Interior.Color
Target.Font.Color = Worksheets("Commercial").Range(cellref).Font.Color

End If




...but it halts on the target.interior.colour line. cellref still contains the value of the referenced cell, not the cell reference.

 

[GlennUK]

it is:

Target.Interior.Color = cellref.Interior.Color
Target.Font.Color = cellref.Font.Color


Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[thefear]

Glenn, Combo and Skip,

Thank-you very much for your efforts. You've all got me to the answer, so you can all have a star. I've learned something here as well as getting just what I need, so once again, thank-you!

Best Regards
Mark

 

[SkipVought]

[b]
Set[/b] cellref = Worksheets("Commercial").Range("Status_Options").Find(Target)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

cellref is a range (object), so:
[!]Set[/!] cellref = Worksheets("Commercial").Range("Status_Options").Find(Target)


combo

 

[SkipVought]

Check out

Creating Object Variables

in VBA Help


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]