Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mk Color equal to serial number (Excel 97 SR2)

    I'm trying to write a conditional statement that will return a particular grade based upon values or colors entered in certain cells. It's quite easy to do it for the values but I was wondering if there was a way to have a cell that has been formatted a particular color and based on that color assign it a numeric value in an adjacent cell. For example if cell A1 is formatted green then return a value of 1 in cell B1. Any thoughts or ideas would be much appreciated.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mk Color equal to serial number (Excel 97 SR2)

    This User Defined Function will return the color index of the cell passed as an argument:

    <pre>Public Function GetColorNum(oCell As Range) As Long
    Application.Volatile
    GetColorNum = oCell.Interior.ColorIndex
    End Function
    </pre>


    This will return a 4 if you pass it a cell formatted green. If you want to return your own defined values for different colors, then you will need something like this:

    <pre>Public Function GetColorNum(oCell As Range) As Long
    Application.Volatile
    Select Case oCell.Interior.ColorIndex
    Case 4
    GetColorNum = 1
    Case 3
    GetColorNum = 2
    Case 5
    GetColorNum = 3
    Case Else
    GetColorNum = 0
    End Select
    End Function
    </pre>


    Unfortunately, excel does not recognize a color change as a recalculate event. Therefore, after the cell's color changes, you will either have to hit F9 to force a recalculate or change the value of a cell.
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mk Color equal to serial number (Excel 97 SR2)

    Thanks Legare!! I will see if I can get this to work with my situation. I have to look at several ranges and take their results to come up with an overall grade. I will post my findings later. Thank you again!!

  4. #4
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mk Color equal to serial number (Excel 97 SR2)

    Legare,

    Is there a way to do this in Excel w/o using VB? I'm afraid that my knowledge of VB is not good enough to implement what I'm trying to do and I hate taking up too much of your time. Any help would be much appreciated.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mk Color equal to serial number (Excel 97 SR2)

    Not that I know of. The CELL() function will tell you if a cell has a format that will change the cell's color for negative values, but it won't tell you to what color.

    If you want to enter the values in a cell, and then change that cell's or another cell's color based on the value, then you could use conditional formatting. However, that would be limited to four colors (normal color plus three conditional colors).
    Legare Coleman

  6. #6
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mk Color equal to serial number (Excel 97 SR2)

    I thought it might be helpful if I included an attachement of the file showing what I'm trying to accomplish. I need to be able to come up with an overall grade that looks at for example, cells (I13 thru AD13). The columns that are giving me the challenges are cells (S,Y,AA,& AD). These cells are being formatted based upon conditions in the Procedure Sub ReportcardsVB_CY(). I would like to insert columns adjacent to S,Y, and AA for example that when the function you recommended is passed it will return a numeric value in the adjacent column which could then be used in a formula. Is this possible and I'm I making any sense? Thanks again!!
    Attached Files Attached Files

  7. #7
    mike-tenn
    Guest

    Re: Mk Color equal to serial number (Excel 97 SR2)

    I don't know if this will help: I had a spreadsheet that contained color filled cells to indicate condition (red/yellow/green) and I needed to change these to numerical values. A contributor to WOPR (I lost his name, sorry!) provided the following code:
    *******************
    Try this code (select the area you want to modify) then run ColoursToNumbers

    Sub ColoursToNumbers()
    Dim cll As Range, rng As Range

    Set rng = ActiveWindow.Selection

    For Each cll In rng.Cells
    Select Case cll.Interior.Color
    Case vbRed
    cll = 2.5
    Case vbYellow
    cll = 5
    Case vbGreen
    cll = 8
    End Select
    Next

    End Sub
    *****************
    hope this helps!!

    Mike in Tennessee

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Mk Color equal to serial number (Excel 97 SR2)

    Just something that you should be aware of, if the color of a cell is due to conditional formatting, then Legare's code will not return the correct value. It will return the underlying color, i.e. the color that would pertain if no conditional formatting was active.

    Andrew C

  9. #9
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mk Color equal to serial number (Excel 97 SR2)

    Thank you Mike for the code and Andrew for the head's up on the formatting. I just got out of a meeting so I'm looking at Mike's code now to see if it would work for my situation and my skill level at implementing it. Thanks again!!

  10. #10
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mk Color equal to serial number (Excel 97 SR2)

    This is the code I came up with modifying Mike's code slightly. It doesn't work. It just steps thru the code but it doesn't find the range nor do I see the offset property working. Can anyone help? Thanks!!

    ********************************************

    Sub ColoursToNumbers()
    Dim iCell As Range
    For Each iCell In Range("AR_Over_Short_Condtl")
    If iCell.Value <> "" Then
    Select Case iCell.Interior.Color

    Case vbRed
    iCell.Offset(0, 1) = 3
    Case vbYellow
    iCell.Offset(0, 1) = 2
    Case vbGreen
    iCell.Offset(0, 1) = 1
    End Select
    End If
    Next iCell
    End Sub

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mk Color equal to serial number (Excel 97 SR2)

    I am attaching your workbook showing how I would solve this problem. I added a new sheet named Grades where I placed tables for each of the columns in question. In cells 21 through 24 of columns P, S, Y, AA, and AD I inserted formula that will look up the value in the cell and return the number corresponding to the cell color as set by the macro. The value ranges in the macro are hard coded, so I assumed that they will not change very often, and if and when they do the tables on the Grades sheet could be manually updated. If this is not the case, then the macro could be modified to insert the required values in the tables on the Grades sheet.
    Attached Files Attached Files
    Legare Coleman

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Mk Color equal to serial number (Excel 97 SR2)

    I don't wish to labour a point , but that code works fine. The problem is the Interior.Color property will not return conditional formatting attributes. Change the underlying color of that range to red (it wont show due to the CF) and running th ecode will result in the value 3 being placed in the adjacent cell)

    Given that the cell color is based on a condition, why not test for that condition rather than the color.

    Andrew C

  13. #13
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mk Color equal to serial number (Excel 97 SR2)

    Good morning!! Andrew, are you speaking of the code I pasted above or Legare's approach? Legare, I didn't see your post until this morning but I will take a look at it. Thanks!!

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Mk Color equal to serial number (Excel 97 SR2)

    I am speaking of the code you posted yourself. That is fine for returning the color of a cell provided it is not a color derived as a result of conditional formatting. Thee only way (that I know of) to get the conditional color by code would be to a) establish the formatting for all conditions set, and [img]/forums/images/smilies/cool.gif[/img] establish the condition pertaining to the cell, then c) derive from that the actual color of the cell. I just think it is probably best to test for the conditions and base your calculations on those rather that concerning yourself with th ecolor.

    The original code Legare posted (post 57362) would not be suitable for conditional formatting, and I am sure that he did not intend that it would be.

    I have not looked at the solution he provided (by way of attachment) in his last post, but am sure it is a solution to the problem as he sees it.

    Andrew C

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mk Color equal to serial number (Excel 97 SR2)

    You are absolutely correct. Conditional Formatting was not mentioned in the original question, and my code was not meant to deal with it, and will not work for the conditional formatting. The solution I posted yesterday is the same as your recommendation to use the value in the cell that the conditional formatting is testing to derive the desired grade codes.
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

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