-
May 9th, 2012, 05:03 PM
#1
Thread Starter
Lively Member
[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!
-
May 10th, 2012, 05:25 AM
#2
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
-
May 11th, 2012, 01:59 PM
#3
Thread Starter
Lively Member
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.
-
May 11th, 2012, 03:38 PM
#4
Thread Starter
Lively Member
Re: Resetting font color in excel after update
Actually this works. Is it possible to only update if user clicks an "update button"?
-
May 11th, 2012, 04:43 PM
#5
Thread Starter
Lively Member
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
-
May 11th, 2012, 08:58 PM
#6
Re: Resetting font color in excel after update
dont use Worksheet_Change event, use only CommandButton1_Click event
-
May 12th, 2012, 05:11 AM
#7
Thread Starter
Lively Member
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.
-
May 12th, 2012, 05:15 AM
#8
Re: Resetting font color in excel after update
make a sub program, then cal it from Worksheet_Change event and CommandButton1_Click event.
-
May 12th, 2012, 05:31 AM
#9
Thread Starter
Lively Member
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:
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
Last edited by Ramaseko; May 12th, 2012 at 05:37 AM.
-
May 12th, 2012, 05:38 AM
#10
Re: Resetting font color in excel after update
why u need a command button when u can do it by worksheet change event?
-
May 12th, 2012, 05:41 AM
#11
Thread Starter
Lively Member
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!
-
May 12th, 2012, 05:46 AM
#12
Re: Resetting font color in excel after update
if posible attach the file, i hav only excel 2003
-
May 12th, 2012, 06:06 AM
#13
Thread Starter
Lively Member
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
-
May 12th, 2012, 07:52 AM
#14
Re: Resetting font color in excel after update
i hav excel 2003 only, this looks higher version, change that to lower version
-
May 12th, 2012, 08:24 AM
#15
Thread Starter
Lively Member
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
-
May 12th, 2012, 08:44 AM
#16
Re: Resetting font color in excel after update
there is no date? wher do u enter date?
-
May 12th, 2012, 08:53 AM
#17
Thread Starter
Lively Member
Re: Resetting font color in excel after update
oops! Sorry!. Find attached.
Note..This is just some random data.
sample.zip
-
May 12th, 2012, 09:02 AM
#18
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.
-
May 12th, 2012, 09:33 AM
#19
Thread Starter
Lively Member
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.
-
May 13th, 2012, 03:00 AM
#20
Addicted Member
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.
-
May 13th, 2012, 07:03 AM
#21
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|