Using VB Code to change cell colour on back of formula

**Twaddy** · 09-28-2010, 06:48 PM

Hi

Thanks very much for all your help and advise, as you can tell my knowledge around VB is almost non existent so your time is much appreciated.

Looks like I'll have to look for an alternative solution, like getting my company to upgrade Office!

Cheers

**Marcol** · 09-28-2010, 07:23 PM

Hi Twaddy

It has been suggested several times in the replies to your thread, that you should post a sample workbook showing what you have so far.

There is little or no reason why most of the replies you have recieved should not solve your problem, so I can only conclude that your actual workbook is a bit more involved than you are admitting.

If you post a desensitised workbook then, just perhaps, we could not only solve your problem and save your company contributing to Mr William Henry "Bill" Gates IIIs' personal wealth and hey! your boss might even throw a token of gratitude towards yours.....

**Twaddy** · 09-29-2010, 10:40 AM

Originally Posted by Marcol

Hi Twaddy

It has been suggested several times in the replies to your thread, that you should post a sample workbook showing what you have so far.

There is little or no reason why most of the replies you have recieved should not solve your problem, so I can only conclude that your actual workbook is a bit more involved than you are admitting.

If you post a desensitised workbook then, just perhaps, we could not only solve your problem and save your company contributing to Mr William Henry "Bill" Gates IIIs' personal wealth and hey! your boss might even throw a token of gratitude towards yours.....

Hi

I was only asked once to my knowledge to post a dummy workbook which I did in post No 8, perhaps I didn't post correctly or perhaps you missed it.

My understanding from the replies are that a worksheet change event can not run alongside a worksheet calculate and also that a formula cannot trigger a worksheet change event.

If I've misunderstood then I apologise.

I really do appreciate all the help I've received

Thanks

Originally Posted by Leith Ross

Hello Twaddy,

The Worksheet_Change event is fired when either the user types in the cell and hits return or a user makes a selection from a Validation drop down list.

However, when a cell's content is changed by a Formula, a cell link, a DDE conversation, a Forms drop down, or an ActiveX drop down then the event is NOT fired.

**Leith Ross** · 09-29-2010, 05:18 PM

Hello Twaddy,

I downloaded your workbook and had a look at what you were doing. After doing some tests, the results proved I was in error. The LOOKUP formula does trigger the Worksheet_Change event.

I made corrections to the formulae in column "AD" to prevent the decision by zero error. An If statement is all that was needed.

=IF(AB4 <>0,AC4/AB4,0)

The macro code was askew and that has been corrected also. Here is the corrected code. All changes mentioned have been added to the attached workbook.

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim c As Range
  Dim icol As Long
  
    Beep
    If Target.Cells.Count > 1 Then Exit Sub

    If Not Intersect(Target, Union(Range("AF4:AF18"), Range("E4:N18"))) Is Nothing Then
       Select Case UCase(Target.Value)
         Case "SILVER": icol = 15
         Case "BRONZE": icol = 46
         Case "AMBER": icol = 44
         Case "RED": icol = 3
         Case "GOLD": icol = 6
         Case "CVP": icol = 1
         Case Else: icol = xlNone
       End Select
       Target.Interior.ColorIndex = icol
    End If

End Sub

**Twaddy** · 09-30-2010, 04:29 PM

Hi Leith

Thanks for taking the time to look at my litle problem, much appreciated.

Something which I don't think I mentioned on previous posts or on the workbook was the bandings for differing grades in column AF which are as follows

Red - 0% to 24%
Bronze - 25% - 62%
Silver 63% - 79%
Gold 80% - 100%

I've tweaked the formula in AF to reflect this as there isn't a banding for Amber. The formula appears to work ok and the correct grades populate depending on the score however I'm not sure if by doing so it's had a knock on effect to the code as the cell colours in AF aren't formatting correctly based on the text

Would you mind taking a look to see if you can spot anything obvious?

Thanks again

I've attached an updated version of the worksheet with the amended formula

Thread: Using VB Code to change cell colour on back of formula

Thread Tools

Display

Re: Using VB Code to change cell colour on back of formula

Re: Using VB Code to change cell colour on back of formula

Re: Using VB Code to change cell colour on back of formula

Re: Using VB Code to change cell colour on back of formula

Re: Using VB Code to change cell colour on back of formula

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions