[RESOLVED] Resetting font color in excel after update-VBForums
Results 1 to 21 of 21

Thread: [RESOLVED] Resetting font color in excel after update

  1. #1

    Thread Starter
    Lively Member
    Join Date
    May 2008
    Posts
    84

    Resolved [RESOLVED] Resetting font color in excel after update

    Hi guys!

    I have a workbook that has 4+ sheets. I have inserted a button on one of the sheets which should reset font color to black for dates only in specific columns (a-f) on sheet 2. The resetting should only happen if user has updated anything in workbook. I have tried recording a macro and it seems what I want is more complicated than a recorded macro. Has anyone got any ideas on how I can get this working?

    Many thanks!

  2. #2
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,158

    Re: Resetting font color in excel after update

    try something like this
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    For Each cel In UsedRange
        If IsDate(cel) Then
            cel.Font.Color = Default 'or vbBlack
        End If
    Next
    End Sub
    change the UsedRange to ur range
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  3. #3

    Thread Starter
    Lively Member
    Join Date
    May 2008
    Posts
    84

    Re: Resetting font color in excel after update

    Many thanks for the code suggestion Seenu_1st.
    Sorry I'm not a novice when it comes to macros. I have got something like this now:
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim usedRange As Range
    Set usedRange = Range("A4:C8")
    
    For Each cel In UsedRange
        If IsDate(cel) Then
            cel.Font.Color = vbRed
        End If
    Next
    End Sub
    Does this macro look ok? If I go into my worksheet and insert an image and right click to assign this macro I can't see this macro.

  4. #4

    Thread Starter
    Lively Member
    Join Date
    May 2008
    Posts
    84

    Re: Resetting font color in excel after update

    Actually this works. Is it possible to only update if user clicks an "update button"?

  5. #5

    Thread Starter
    Lively Member
    Join Date
    May 2008
    Posts
    84

    Re: Resetting font color in excel after update

    Code:
    Option Explicit
    Dim fChange As Boolean
    
    Private Sub CommandButton1_Click()
    fChange = Not fChange
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim UsedRange As Range
    Dim cel as Range
    
    If fChange Then
    Set UsedRange = Range("E8:J26")
    For Each cel In UsedRange
      If IsDate(cel) Then
         cel.Font.Color = vbRed
        End If
    Next
    
    End If
    End Sub
    I have tried this but when I click button font doesn't change

  6. #6
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,158

    Re: Resetting font color in excel after update

    dont use Worksheet_Change event, use only CommandButton1_Click event
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  7. #7

    Thread Starter
    Lively Member
    Join Date
    May 2008
    Posts
    84

    Re: Resetting font color in excel after update

    Thanks! seenu_1st. I think I'd still need 2 sub routines. I say this because I want the commandbutton1 click to update used range ONLY if there's been a change on worksheet therefore I reckon I still need the Worksheet_Change event.

  8. #8
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,158

    Re: Resetting font color in excel after update

    make a sub program, then cal it from Worksheet_Change event and CommandButton1_Click event.
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  9. #9

    Thread Starter
    Lively Member
    Join Date
    May 2008
    Posts
    84

    Re: Resetting font color in excel after update

    I have created the following sub programs. However if I click button after changes to worksheet the cell fonts don't change. I know the first program is working as I've used msgbox and everytime I click on sheet it displays message.
    vb Code:
    1. Option Explicit
    2. Dim rngChanged As Range
    3. Private Sub Worksheet_Change(ByVal Target As Range)
    4.     If Target.Count = 1 Then
    5.         If Target <> "" Then
    6.             If Not Intersect(Range("E8:J26"), Target) Is Nothing Then
    7.                 If rngChanged Is Nothing Then
    8.                     Set rngChanged = Target
    9.                 Else
    10.                     Set rngChanged = Union(rngChanged, Target)
    11.                 End If
    12.             End If
    13.         End If
    14.     End If
    15.     End Sub
    16.  
    17. Private Sub CommandButton1_Click()
    18. Dim cel As Range
    19.  
    20.     If Not rngChanged Is Nothing Then
    21.            For Each cel In rngChanged
    22.              If IsDate(cel) Then cel.Font.Color = vbRed
    23.            Next cel
    24.         Set rngChanged = Nothing
    25.     End If
    26.  
    27. End Sub
    Last edited by Ramaseko; May 12th, 2012 at 05:37 AM.

  10. #10
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,158

    Re: Resetting font color in excel after update

    why u need a command button when u can do it by worksheet change event?
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  11. #11

    Thread Starter
    Lively Member
    Join Date
    May 2008
    Posts
    84

    Re: Resetting font color in excel after update

    Because I only want the the usedRange to update font colour after user clicks command button else the font stays the same.

    Many thanks!

  12. #12
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,158

    Re: Resetting font color in excel after update

    if posible attach the file, i hav only excel 2003
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  13. #13

    Thread Starter
    Lively Member
    Join Date
    May 2008
    Posts
    84

    Re: Resetting font color in excel after update

    Hi Seenu. Find attached. This is just a sample where I want commandButton click to update C3:E6 range.

    Many thanks!
    chidren results.zip

  14. #14
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,158

    Re: Resetting font color in excel after update

    i hav excel 2003 only, this looks higher version, change that to lower version
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  15. #15

    Thread Starter
    Lively Member
    Join Date
    May 2008
    Posts
    84

    Re: Resetting font color in excel after update

    I have saved the file in 97-2003 . It's not macro enabled though as I could not find an option to save a macro enabled workbook in 97-2003.

    Please simply open and save as macro enabled on your desktop. The code I currently have is:
    Code:
    Option Explicit
    Dim rngChanged As Range
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Count = 1 Then
            If Target <> "" Then
                If Not Intersect(Range("C3:E6"), Target) Is Nothing Then
                    If rngChanged Is Nothing Then
                        Set rngChanged = Target
                    Else
                        Set rngChanged = Union(rngChanged, Target)
                    End If
                End If
            End If
        End If
        End Sub
     
    Private Sub CommandButton1_Click()
    Dim cel As Range
     
        If Not rngChanged Is Nothing Then
               For Each cel In rngChanged
                 If IsDate(cel) Then cel.Font.Color = vbRed
               Next cel
            Set rngChanged = Nothing
        End If
     
    End Sub
    Thanks for your help again!
    sample.zip

  16. #16
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,158

    Re: Resetting font color in excel after update

    there is no date? wher do u enter date?
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  17. #17

    Thread Starter
    Lively Member
    Join Date
    May 2008
    Posts
    84

    Re: Resetting font color in excel after update

    oops! Sorry!. Find attached.

    Note..This is just some random data.

    sample.zip

  18. #18
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,158

    Re: Resetting font color in excel after update

    input date like this 18/3/2011 or 18-3-2011
    18.3.2011 this is not working dont use dot

    edited: input as month/day/year
    Last edited by seenu_1st; May 12th, 2012 at 09:11 AM.
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  19. #19

    Thread Starter
    Lively Member
    Join Date
    May 2008
    Posts
    84

    Re: Resetting font color in excel after update

    Thanks! I'm currently out and will try this when i get back home and will let you know how i get on! Thanks for your great help.

  20. #20
    Addicted Member
    Join Date
    Jun 2009
    Location
    Townsville, Qld, Australia
    Posts
    135

    Re: Resetting font color in excel after update

    Aside: If your users are compulsive date dotters, you can always look at inserting a routine to change any dots in the date range (your A4:C8 above) to slashes or hyphens. It's a little extra work but it does make the macros more reliable.

  21. #21

    Thread Starter
    Lively Member
    Join Date
    May 2008
    Posts
    84

    Re: Resetting font color in excel after update

    Many thanks guys. I tried changing the date format and yes it works Seenu.
    Resource Dragon, I'll try and put something that converts the dot to slashes or hyphens.

    Really appreciate all your help. You saved me a great amount of time resources.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.