Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Feb 2008
    Location
    Chertsey, Surrey, United Kingdom
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    More than 3 Conditional Formats (Office 2003 Service Pack 2)

    Hello

    I am tring to get around the Excel 3 Conditional Format settings and I happened across this code which works OK with numbers but I would like to work with letters, I tried ISTEXT instead of ISNUMERIC but it does not like it, what do I need to change to make it conditional if say an X was in the cell, also I have non contigeous ranges that I wish to format the same, for example B7 - F61, B63 - F117 etc. how would I change this line - For Each cel In Range("A710").Cells - to reflect the different ranges

    For Each cel In Range("A710").Cells

    If IsNumeric(cel.Value) And cel.Value <> "" Then

    If cel.Value < 0 Then

    cel.Font.ColorIndex = 3

    cel.Interior.ColorIndex = 0

    ElseIf cel.Value < 100 Then

    cel.Font.ColorIndex = 2

    cel.Interior.ColorIndex = 1

    ElseIf cel.Value < 500 Then

    cel.Font.ColorIndex = 1

    cel.Interior.ColorIndex = 4

    ElseIf cel.Value < 1000 Then

    cel.Font.ColorIndex = 4

    cel.Interior.ColorIndex = 1

    Else '>=1000

    cel.Font.ColorIndex = 1

    cel.Interior.ColorIndex = 3

    End If

    Else

    cel.Font.ColorIndex = 1

    cel.Interior.ColorIndex = 0

    End If

    Next

    End Sub

    Thank you

    Roger

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More than 3 Conditional Formats (Office 2003 Service Pack 2)

    You can use code like this:

    For Each cel In Range("B7:F61,B63:F117").Cells
    Select Case cel.Value
    Case "This"
    cel.Font.ColorIndex = 3
    Case "That"
    cel.Font.ColorIndex = 6
    Case "Other"
    cel.Font.ColorIndex = 4
    Case Else
    cel.Font.ColorIndex = 5
    End Select
    Next cel

  3. #3
    Lounger
    Join Date
    Feb 2008
    Location
    Chertsey, Surrey, United Kingdom
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More than 3 Conditional Formats (Office 2003 Service Pack 2)

    Excellent

    Thanks for the prompt response

  4. #4
    Lounger
    Join Date
    Feb 2008
    Location
    Chertsey, Surrey, United Kingdom
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More than 3 Conditional Formats (Office 2003 Service Pack 2)

    Hello

    If I may, 2 more questions please, in the macro below I have defined the range of cells that I wish to applying the formatting to, however

    a) If for example I put A in H7 and drag it down to the last cell in the range H781 it does, as expected colour all of the cells and put the letter A in them, however when I go back to delete A in the cells between the ranges, H68 to H71 for example the letter A is removed but the cell remains the colour it was not as I expected with the 'Case Else' staement at the end of the Macro

    If I wanted the cells to recognise both capital and non capital letters would I use "A,a" as I cannot get it to work

    Private Sub Worksheet_Change(ByVal Target As Range)
    For Each cel In Range("H7:AL67,H72:AL132,H137:AL197,H202:AL262,H26 6:AL326,H331:AL391,H396:AL456,H461:AL521,H526:AL58 6,H591:AL651,H656:AL716,H721:AL781").Cells
    Select Case cel.Value
    Case "A"
    cel.Font.ColorIndex = 2
    cel.Interior.ColorIndex = 3
    Case "B"
    cel.Font.ColorIndex = 2
    cel.Interior.ColorIndex = 7
    Case "C"
    cel.Font.ColorIndex = 2
    cel.Interior.ColorIndex = 10
    Case "D"
    cel.Font.ColorIndex = 2
    cel.Interior.ColorIndex = 11
    Case "E"
    cel.Font.ColorIndex = 2
    cel.Interior.ColorIndex = 17
    Case "F"
    cel.Font.ColorIndex = 2
    cel.Interior.ColorIndex = 1
    Case "G"
    cel.Font.ColorIndex = 2
    cel.Interior.ColorIndex = 29
    Case "H"
    cel.Font.ColorIndex = 2
    cel.Interior.ColorIndex = 9
    Case "I"
    cel.Font.ColorIndex = 2
    cel.Interior.ColorIndex = 56

    Case Else
    cel.Font.ColorIndex = 5
    cel.Interior.ColorIndex = 0
    End Select
    Next cel

    End Sub

    Thank you

    Roger

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: More than 3 Conditional Formats (Office 2003 Service Pack 2)

    a) Your code does nothing to the cells not in the range your specify since you explicitly exclude them. They are not in the range:
    ("H7:AL67,H72:AL132,H137:AL197,H202:AL262,H266:AL3 26,H331:AL391,H396:AL456,H461:AL521,H526:AL586,H59 1:AL651,H656:AL716,H721:AL781")

    You add the formatting since you are copying cells which are already formatted, so changing the value will not trigger the case select to do anything with them. To remove must be done manually or add those cells to the ranges. You have explicitly include them if you want the code to change them.

    [note: If you copy the "A" from a cell that has no formatting to it, the excluded cells will never get formatted so they don't have to be changed.]

    The line:
    Select Case UCase(cel.Value)

    will make the changes non case-sensitive

    Steve

  6. #6
    Lounger
    Join Date
    Feb 2008
    Location
    Chertsey, Surrey, United Kingdom
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More than 3 Conditional Formats (Office 2003 Service Pack 2)

    Hello

    Thank you for your response, the Case change worked fine but I am still a little puzzled to the (a) answer, say for example I used ("A1:A10,A15:A25"), If I type A1=A, A2=B and keep repeating down the cells A11:A14 remain blank because they are excluded from the formula, if however I typed A in A1 then used the grab handle to drag the cell down to A20 it will fill alll of the cells with the formatting of A1, if I then go back and delete the A from A11-A14 I would expect the cell to be blanked because of the Change Else statement at the end of the formula (Transparent cell / Black font) but it does not, in fact if I type any letter into A11-A14, for example a Z, the Z shows white with the formatted background still in place. ??

    Thank you


    Roger

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: More than 3 Conditional Formats (Office 2003 Service Pack 2)

    But it never gets the "case Else" since you are not checking the contents of those cells. You are only checking the contents of the the cells: "A1:A10,A15:A25"

    A11:A14 are never tested in the "Case Select". Your code only checks the values in the range of cells from "A1:A10,A15:A25"

    Steve

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More than 3 Conditional Formats (Office 2003 Service Pack 2)

    In addition to Steve's reply, you must decide what you want: either include A11:A14 in the range and have these cells colored automatically, or exclude A11:A14 from the range and have these cells *not* colored automatically. You can't have both...

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: More than 3 Conditional Formats (Office 2003 S

    Since you have had these questions, it made me rethink a question I had and just presumed something and I am no longer sure that presumption is accurate.

    As your code is written, at any and all changes on the sheet (ie whether anything within your explicitly defined range is changed or not) you will loop through all the cells in that range and reset the color.

    I presumed that you were doing this since you may have formulas in that range and you want to update when the value may change (which could be dependent on changing cells outside the range). If this is not the case, instead of looping through all the cells, you may want to only loop through the cells that are in the range intersected by that range and the cells that changed and/or even the cells in that range with formulas.

    This will make the worksheet less "sluggish"

    Steve

  10. #10
    Lounger
    Join Date
    Feb 2008
    Location
    Chertsey, Surrey, United Kingdom
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More than 3 Conditional Formats (Office 2003 S

    Hello

    Thanks Hans and Steve, I must sound really dense, sorry but I still do not quite get it, in the formula I am saying I want these particular cells filled with a colour according to the letter inserted, therefore if I accidentaly drag a cell and it takes the colour from the formatting above, I would have assumed that when I removed the letter by deleting it the cell would revert to its original blank state. However this is now academical as all of the cells will be locked and unlocked when updating. I do now though have another problem when I am trying to update my worksheet with this code from a user form.

    this is the code that I have in to 'Worksheet Change' event

    Private Sub Worksheet_Change(ByVal Target As Range)

    For Each cel In Range("H7:AL780").Cells

    Select Case UCase(cel.Value)

    Case "S"
    cel.Font.ColorIndex = 2
    cel.Interior.ColorIndex = 4
    Case "S1"
    cel.Font.ColorIndex = 2
    cel.Interior.ColorIndex = 10
    Case "S2"
    cel.Font.ColorIndex = 2
    cel.Interior.ColorIndex = 42
    Case "S3"
    cel.Font.ColorIndex = 2
    cel.Interior.ColorIndex = 50

    Case Else
    cel.Font.ColorIndex = 1
    End Select
    Next cel

    End Sub

    When I submit my data (4 fields that update calander events) it gives me the error below and highlights - Select Case UCase(cel.Value)

    Any ideas why please
    Attached Images Attached Images

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More than 3 Conditional Formats (Office 2003 S

    After clicking Debug, hover the mouse pointer over cel.Value. What do you see?

  12. #12
    Lounger
    Join Date
    Feb 2008
    Location
    Chertsey, Surrey, United Kingdom
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More than 3 Conditional Formats (Office 2003 S

    Hello

    when it is highlighted the mouse cursor is a bar and says nothing when I hover over it or any other line of the code it does not say anything, if it helps this happens if I now type anything in any cell of the work book not just those explicitly mentioned in the formula?

    Thank you

    Roger

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More than 3 Conditional Formats (Office 2003 S

    Could you attach a sample workbook?

  14. #14
    Lounger
    Join Date
    Feb 2008
    Location
    Chertsey, Surrey, United Kingdom
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More than 3 Conditional Formats (Office 2003 S

    Hello

    Thanks for your attention, please find attached a zip file that hopefully shows the problem

    Roger
    Attached Files Attached Files

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More than 3 Conditional Formats (Office 2003 S

    You have a whole series of cells whose formulas result in errors, so cel.Value will cause an error. You can get around it like this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    For Each cel In Range("H7:AL10").Cells
    If IsError(cel.Value) Then
    ' do you want to color these?
    Else
    Select Case UCase(cel.Value)
    Case "S"
    cel.Font.ColorIndex = 2
    cel.Interior.ColorIndex = 4
    Case "S1"
    cel.Font.ColorIndex = 2
    cel.Interior.ColorIndex = 10
    Case "S2"
    cel.Font.ColorIndex = 2
    cel.Interior.ColorIndex = 42
    Case "S3"
    cel.Font.ColorIndex = 2
    cel.Interior.ColorIndex = 50
    Case Else
    cel.Font.ColorIndex = 1
    End Select
    End If
    Next cel
    End Sub

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
  •