Change font colour Entire sheet or workbook
Simple one hopefully, just not for me!
I want some VBA code that runs in the background to look at the entire worksheet or workbook and any cell that has a value of 5 will turn Green (Font.Color = vbGreen)
Please put me out of my misery. I just cant figure it out how to get this.
Re: Change font colour Entire sheet or workbook
I have managed to piece together the below and as my data is in one large table called 'Track' I have managed to define the range as this table. Still doesn't work though.... any ideas please?
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Track")) Is Nothing Then
For Each Cell In Range("Track")
If Cell.Value = "5" Then Cell.Font.Color = RGB(300, 0, 105)
Next Cell
End If
End Sub
You have a Worksheet_Change macro which will be triggered each time you make a change in any cell in your sheet. As written it will loop through all the cells. Is that what you want or do you simply want the cell color to change to green any time you enter a "5" in a cell?
You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
Practice makes perfect. I'm very far from perfect so I'm still practising.
Hi, Yes I would like it to run through the whole table each time, but the coding as I have written it above returns a ''run-time error '13' type mismatch''
(It does change the first instance of the number 5 it finds to the new colour but then errors out)
Think I must have something small missing from the code..... does anything jump out to you?
Last edited by mattydboom1; 01-29-2019 at 04:53 PM.
I don't understand why you would want the code to loop through all the cells every time you make a change in any cell. If the cells that contain "5" are already green, then the only cells that would need to change to green would be any cell which currently doesn't have a "5" and a "5" is entered in that cell. Does this make sense? By the way, is the number "5" entered manually or is it the result of a formula?
OK - I see your logic, and happy to do that way if you could steer me on the code.
Eventually the number five will be entered by way of drop down list but for now I am just trying to get working by entering manually. Thanks for your help.
This should work regardless of whether you enter manually or by way of a drop down. If you enter it manually, exit the cell after entering it.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks