If statement where criteria is based on a format

shaunhh · #1 (**permalink**) 09-22-07, 05:19

Hi,
I can't figure out how to create an if statement where the criteria is based on a format such as filling in the background of a cell with a colour.

What I want to be able to do is when you colour in a cell, then a numerical value is created in that cell.

Is this possible to do without any vbscript? Or will I have to make a macro for this, and if so what would it look like?

Thank you.

Any help would be appretiated.. I'm stumped.

shades · #2 (**permalink**) 09-22-07, 20:47

Howdy, and welcome to the board.

For this you would need VBA code. I'll look around, and see what I have.

shaunhh · #3 (**permalink**) 09-22-07, 22:19

Thanks Shades, I appretiate it.

shaunhh · #4 (**permalink**) 09-27-07, 20:31

*bump*

no luck?

shades · #5 (**permalink**) 09-28-07, 10:05

Sorry, work has been overwhelming. And my younger brother had a heart attack and surgery last night, so am not concentrating much on XL.

gvee · #6 (**permalink**) 09-28-07, 11:09

Deepest sympathies Shades. I hope your brother gets well soon!

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If IsNumeric(Target.Value) Then
        Select Case Target.Value
            Case 1
                Target.Interior.ColorIndex = 0
            Case 2
                Target.Interior.ColorIndex = 1
            Case 3
                Target.Interior.ColorIndex = 2
            Case 4
                Target.Interior.ColorIndex = 3
            Case 5
                Target.Interior.ColorIndex = 4
            Case 6
                Target.Interior.ColorIndex = 5
            Case 7
                Target.Interior.ColorIndex = 6
            Case Else
                Target.Interior.ColorIndex = 7
        End Select
    Else
        Target.Interior.ColorIndex = 0
    End If
    
    
End Sub

shaunhh · #7 (**permalink**) 09-29-07, 21:19

Im sorry to hear about that Shades, I hope your brother is ok.

georgev, isnt the code you gave me the same as using the conditional formatting tool in excel?
What I wanted to do was the opposite, have the colour formatting based in the If criteria, then have it insert values into the cells based on the colour formatting.

example: I change the backgound in the cell to blue, then the contents of the cell are changed to 17.5, else if the background is changed to yellow, change the cell contents to 15

From the code you have given me though I think I can figure out how to do it so thanks for your help.

gvee · #8 (**permalink**) 09-30-07, 08:50

Conditional formatting only allows 3 conditions. Anyhow, you can simply switch the case statement around to be

Code:

Select Case Target.Interior.ColorIndex
...

Good luck!

shades · #9 (**permalink**) 10-04-07, 17:23

Thanks, georgev, for taking up the mantel. My brother was finally released from the hospital and after a period of exercise, etc. he will go back to work in 2 weeks. And now my work is moving me to a new building tomorrow, so more change; never seems to change.

gvee · #10 (**permalink**) 10-05-07, 03:34

Glad to hear that you're brother is on the fast track to recovery! Good luck with the move and all the best

Internet Services

Web Development

Digital Marketing

Consumer Tech