Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: VBA to change font color in cells based on value

  1. #1
    Board Regular KP_SoCal's Avatar
    Join Date
    Nov 2009
    Location
    Southern California
    Posts
    116
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to change font color in cells based on value

    I'm using some basic code below in an on Workbook Open event to format cells with a value less then 2 and less than 1 with a particular color.

    The code works, but it really slows my worksheet down when opening. Is there better way to write this? Thanks!

    Code:
     
    Dim myRange As Range
    Dim cell As Range
    Set myRange = Range("V6:V50000")
        For Each cell In myRange
        If cell.Value < 2 Then cell.Font.ColorIndex = 5
        If cell.Value < 1 Then cell.Font.ColorIndex = 3
        Next
    Last edited by KP_SoCal; Dec 15th, 2009 at 12:17 AM. Reason: forgot dim statements

  2. #2
    MrExcel MVP sanrv1f's Avatar
    Join Date
    Jan 2009
    Location
    Chennai, India
    Posts
    3,468
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to change font color in cells based on value

    do you really need VBA?

    see if this helps

    http://www.contextures.com/xlCondFormat01.html
    regards
    Sankar
    show your sheet on the board, get HTML maker


    யாதும் ஊரே, யாவரும் கேளிர்; தீதும் நன்றும் பிறர்தர வாரா

  3. #3
    Board Regular KP_SoCal's Avatar
    Join Date
    Nov 2009
    Location
    Southern California
    Posts
    116
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to change font color in cells based on value

    Hi Sankar, I'm familiar with conditional formatting. For specific reasons, I can not accomplish what I need using the conditional formatting for the particular spreadsheet I'm working on, which is why I'm resorting to some standard formatting code.



  4. #4
    MrExcel MVP sanrv1f's Avatar
    Join Date
    Jan 2009
    Location
    Chennai, India
    Posts
    3,468
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to change font color in cells based on value

    may be this

    Code:
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Dim myRange As Range
    Dim cell As Range
    Set myRange = Range("V5:V50000")
    myRange.AutoFilter 1, "<2"
    myRange.Parent.AutoFilter.Range.Cells.Font.ColorIndex = 5
    myRange.Parent.AutoFilterMode = False
    myRange.AutoFilter 1, "<1"
    myRange.Parent.AutoFilter.Range.Cells.Font.ColorIndex = 3
    myRange.Parent.AutoFilterMode = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    regards
    Sankar
    show your sheet on the board, get HTML maker


    யாதும் ஊரே, யாவரும் கேளிர்; தீதும் நன்றும் பிறர்தர வாரா

  5. #5
    Board Regular KP_SoCal's Avatar
    Join Date
    Nov 2009
    Location
    Southern California
    Posts
    116
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: VBA to change font color in cells based on value

    Sankar,
    Excellent! It absolutely works and your code executes much faster than my original code. Thanks so much for your help!!!

    KP

  6. #6
    New Member
    Join Date
    Mar 2009
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to change font color in cells based on value

    How can I change this code to color based on a test entry?

    I want to format days of the week to different colors.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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