Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2007
    Posts
    4

    Unanswered: If statement where criteria is based on a format

    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.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy, and welcome to the board.

    For this you would need VBA code. I'll look around, and see what I have.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Sep 2007
    Posts
    4
    Thanks Shades, I appretiate it.

  4. #4
    Join Date
    Sep 2007
    Posts
    4
    *bump*

    no luck?

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Sorry, work has been overwhelming. And my younger brother had a heart attack and surgery last night, so am not concentrating much on XL.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  7. #7
    Join Date
    Sep 2007
    Posts
    4
    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.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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!
    George
    Home | Blog

  9. #9
    Join Date
    Oct 2003
    Posts
    1,091
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Glad to hear that you're brother is on the fast track to recovery! Good luck with the move and all the best
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •