VBA code

[The Idea Dude]

What code would do the following?

There are 5 numbers in different cells (These numbers can change)

A1=2,
A2=6,
A3=9,
A4=35,
A5=3

I need to allocate one colour to the highest number, a different colour to the next highest and so on.

Any suggestions?
This message was edited by The Idea Dude on 2002-09-11 03:49

 

[Andrew Poulsom]

Well, the Change event only occurs when a user changes a cell and a combo box is not a user!

So you need to delete the Worksheet_Change procedure and add this:

Private Sub ComboBox1_Change()
    Call Colours
End Sub

 

[The Idea Dude]

So it looks like this? (the combo box is #17)

Private Sub ComboBox17_Change(ByVal Target As Range)
If Target.Address <> "$R$17" Then Exit Sub
Application.EnableEvents = False
Call Colours
Application.EnableEvents = True
End Sub

I must still be doing something wrong because it is not working yet

 

[Andrew Poulsom]

No, just:

Private Sub ComboBox17_Change()
    Call Colours
End Sub

 

[The Idea Dude]

OK Andrew I think I have this nailed, the colours are changing now, however, if I click colours off, and then change the dropdown box, as expected, colours re-appear.

Here is my attempt at stopping this
And guess what, it didn't work

What's my error

Range("Bll").Select
If Selection.Font.ColorIndex = 1 Then
Range("A1").Select
Else
Call Colours
End If
End Sub

Thanks again,

It is because of people like you that makes learning this stuff so much more fun and easy!!!
This message was edited by The Idea Dude on 2002-09-12 04:54

 

[Andrew Poulsom]

I think you are missing my bit of code that tests if the colour is 1.

' * You already have the next line *
Set Rng = Range("B11:B15")
' * You are missing these lines *
    For Each c In Rng
        If c.Font.ColorIndex = 1 Then Exit Sub
    Next c
' * You already have the next line *
    For Each c In Rng
' * etc etc etc