View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Kris_Wright_77 Kris_Wright_77 is offline
external usenet poster
 
Posts: 24
Default More than 3 Conditional Formats with VBA - Not Working

Tom

I took out the error handler and it still didnt apply the formatting.

Would it make a difference if the ranges are volatile?

The Named Range "tbl_Orig_CostImpact" is specified as
=OFFSET(tbl_Orig_CostImpact_Hdr,1,0):OFFSET(tbl_Or ig_CostImpact_Btm,-1,0)
but the _Hdr and _Btm are both equal to a single cell each.

I have noticed that on some occassions when I have been running the code
line by line, the UDF that I have, which runs after the Worksheet_Change
returns #Name
Does this help identify the problem?

Also, to make absolutely sure that it is not some Conditional Formatting
hanging around from the earlier version, is there a piece of code that
returns whether a cell has Conditional Formatting?

Thanks very much for your help on this.

Kris



"Tom Ogilvy" wrote:

It worked OK for me. Try commenting out your error handler. If one of
your defined ranges does not exist, it will not work. Commenting out the
'On Error GoTo endit

will tell you if this is a problem.

--
Regards,
Tom Ogilvy


"Kris_Wright_77" wrote in message
...
I've double and triple checked, and the Conditional Formats show nothing
applied.
In a much earlier version, I had used conditional formatting, but this
newer
one needs more, so I deleted them. Is there any possibility that it is
still
there?

When I run it line by line everything appears to work fine with the
ScoreOffset and Num taking on the appropriate values.
At the line
Target.Offset(0, ScoreOffset).Interior.ColorIndex = Num
when I hover over it, I get
Target.Offset(0, ScoreOffset).Interior.ColorIndex = -4142
and Num still reads the right value.

After running the line, hovering over
Target.Offset(0, ScoreOffset).Interior.ColorIndex
still gives -4142

I am at a loss.

Is there any other info that I can give that could pinpoint the fault?

Thanks very much

Kris


"JE McGimpsey" wrote:

If everything works *except* applying the format, my best guess is that
you have Conditional Formatting applied to the cells which is masking
your format.

If that's not the case, I think you need to give more information. When
you set a breakpoint on the line that applies the format, does the
routine stop there (or does it exit before that)? Is ScoreOffset what
you expect it to be? Num?

Note that you don't really need to turn off events- applying formats
doesn't trigger an event. Doesn't hurt, but doesn't help either.

In article ,
Kris_Wright_77 wrote:

Hi
Hope someone can help, as I seem to have got myself stuck.

I am currently trying to Conditionally Format a cell based on 4
conditions.
From code I have found on other posts, I have got most of it working.
However, the bit that doesnt work is the most important bit - Applying
the
Format !!

Code is posted below.
As you can see the code is triggered by a change in value on the
worksheet.
However, the cell I want formated is relative to the changed cell, and
is the
main way in which it has been modified from the other helpful posts.
But I
cannot see why this would prevent it working.

Could someone please let me know how to fix it? - I would like to avoid
loading in add-ins etc, as the finished spreadsheet will be used by
many
people.
And, if possible, explain why the error is occurring so I can try and
avoid
doing it again for a.n.other problem I have to solve/automate.

Thanks very much for any help you can give