[RESOLVED] Resetting font color in excel after update

[Ramaseko]

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!

[seenu_1st]

try something like this

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

[Ramaseko]

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:

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.

[Ramaseko]

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

[Ramaseko]

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

[seenu_1st]

dont use Worksheet_Change event, use only CommandButton1_Click event

[Ramaseko]

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.

[seenu_1st]

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

[Ramaseko]

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.

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("E8:J26"), 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

[seenu_1st]

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

[Ramaseko]

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

Many thanks!

[seenu_1st]

if posible attach the file, i hav only excel 2003

[Ramaseko]

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

Many thanks!
chidren results.zip

[seenu_1st]

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

[Ramaseko]

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:

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

[seenu_1st]

there is no date? wher do u enter date?

[Ramaseko]

oops! Sorry!. Find attached.

Note..This is just some random data.

sample.zip

[seenu_1st]

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

[Ramaseko]

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.

[Resource Dragon]

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.

[Ramaseko]

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.