Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change Font Color when criterias are met (XL 2003)

    Hi Loungers,

    A happy new year to all .

    I have wrote this code to loop thru the table and change the font color to red in those cells when its value in Col.3 is < 0.
    The place I got stuck is that I would also want to change the font color of those cells when their value in Col.3 is > 15.
    I have tried adding Or CurCell.Value > 15 Then CurCell.Font.ColorIndex = 5 but it give me an error msg


    Sub Check_Values()
    Dim CurCell As Object
    Dim Rw As Long

    Rw = Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row
    For Each CurCell In Range("C2 : C" & Rw)
    If CurCell.Value < 0 Then CurCell.Font.ColorIndex = 3
    Next
    End Sub

    TIA

    regards, fy
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Change Font Color when criterias are met (XL 2003)

    Try

    If CurCell.Value < 0 Then
    CurCell.Font.ColorIndex = 3
    ElseIf CurCell.Value > 15 Then
    CurCell.Font.ColorIndex = 5
    End If

    BTW you could use Format | Conditional Formatting for this instead of using code.

    PS Happy New Year to you too!

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Font Color when criterias are met (XL 2003)

    Hi Hans,

    Thank for the guide. I am wondering why does Excel show me an error msg as attached if I use a one-liner :

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">ElseIf CurCell.Value > 15 Then</span hi> CurCell.Font.ColorIndex = 5
    End If
    and does not if I use a two-line :

    ElseIf CurCell.Value > 15 Then
    CurCell.Font.ColorIndex = 5
    End If


    Regards, fy

    PS : Thank for the pointer on Conditional Formatting. this is a part of the work that I am evaluating for a project
    and the end users have only rudimentary knowledge in Excel, thus, I want to make it real easy for them.
    Attached Images Attached Images
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Change Font Color when criterias are met (XL 2003)

    If ... Then can be used in two different formats:

    1) The one-line format

    If condition Then some_action

    or

    If condition Then some_action Else other_action

    2) The multi-line format

    If condition Then
    some_action
    End If

    or

    If condition Then
    some_action
    Else
    other_action
    End If

    or

    If condition Then
    some_action
    ElseIf other_condition
    other_action
    End If

    or

    If condition Then
    some_action
    ElseIf other_condition
    other_action
    Else
    yet_another_action
    End If

    You should *not* mix the two formats, that will cause an error message, as you have found.

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

    Re: Change Font Color when criterias are met (XL 2003)

    As Hans stated, you can use conditional formatting to accomplish this and it would be a much better way of doing it. It would make the font change automatic and would be more efficient.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Font Color when criterias are met (XL 2003)

    Hi Hans,

    As always, you do provide the best explanation...better than the MS KnowledgeBase or books. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Font Color when criterias are met (XL 2003)

    Hi Legare

    Happy new year to you. Agreed that conditional formatting would be a better way to do this but if given a data set of approximately 600 rows and close to 60+
    sheets, excel may hang as I have previously encountered.

    Given the dataset, I may encounter the slowiness of the macro execution later on, it is fine but for now.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Font Color when criterias are met (XL 2003)

    I have a similar question. I have a spreasheet that we use to track employee attendance. In cells D12 thru J65 we enter codes to specify what happened with the employee on that day. The codes are either a one, two or three characters long followed by a space and a number (i.e. "v 8", "lp 6.5", "fmv 4.25"). I am currently using conditional formatting to change the background color of the cell based on the alpha characters that were entered in the cell. The problem is I have more codes than conditional formatting will allow. Are there any workarounds or code that may tackle this better?

    For the cells that I want to highlight, I have four different colors that the backgrounds can be changed to, but I also have 18 different codes that need to be reviewed. Any ideas?

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

    Re: Change Font Color when criterias are met (XL 2003)

    You can use the Worksheet_Change event to change the background color automatically when the user enters or edits a value.

    See for example <post:=557,373>post 557,373</post:>.

  10. #10
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Font Color when criterias are met (XL 2003)

    Hi Steve,

    Does the macro below do what you want. This code use the Worksheet _Change event. I am assuming that your codes are in Col A,
    change the col to suit your need. This macro highlight the cells on your specific codes and can get around Excel's three criteria limit for
    Conditional Formatting.

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim icolor As Integer
    If Not Intersect(Target, Range("A:A")) Is Nothing Then ' change the column to your
    Select Case Target
    Case "v 8"
    icolor = 6
    Case "lp 6.5"
    icolor = 3
    Case "fmp 4.25"
    icolor = 7
    Case "fy 100"
    icolor = 43
    Case "tu 69"
    icolor = 26
    Case "Wdy 26"
    icolor = 42
    Case Else
    'Whatever
    End Select
    Target.Interior.ColorIndex = icolor
    End If

    End Sub
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  11. #11
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Font Color when criterias are met (XL 2003)

    Hi Hans,

    I didn't realise that you have posted.....
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Change Font Color when criterias are met (XL 2003)

    No problem, Francis. Different replies can be very useful, because they show different ways of solving a problem.

Posting Permissions

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