Conditional Formatting through Macro
Solved/Closed
smuneeb
Posts
67
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
-
Nov 29, 2015 at 02:28 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Dec 3, 2015 at 06:31 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Dec 3, 2015 at 06:31 AM
Related:
- Conditional Formatting through Macro
- Spell number in excel without macro - Guide
- How to clear formatting in excel - Guide
- Phone formatting software for pc - Download - File management
- Macro excel download - Download - Spreadsheets
- Excel macro to create new sheet based on value in cells - Guide
3 responses
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Nov 30, 2015 at 04:04 AM
Nov 30, 2015 at 04:04 AM
Hello Smuneeb,
I'm not sure what you mean but following is a link to my test work book which shows the code works as it should:-
https://www.dropbox.com/s/9am5x3eeguii930/Smuneeb%28Cond.Format%20Font%20and%20Underline%29.xlsm?dl=0
Cheerio,
vcoolio.
I'm not sure what you mean but following is a link to my test work book which shows the code works as it should:-
https://www.dropbox.com/s/9am5x3eeguii930/Smuneeb%28Cond.Format%20Font%20and%20Underline%29.xlsm?dl=0
Cheerio,
vcoolio.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Nov 30, 2015 at 01:19 AM
Nov 30, 2015 at 01:19 AM
Hello Smuneeb,
Try the following code in a standard module:-
I hope that this helps.
Cheerio,
vcoolio.
Try the following code in a standard module:-
Sub ColourUnderline()
Dim lRow As Long
lRow = Range("E" & Rows.Count).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 5).Value = "A" Or Cells(i, 5) = "Zero" Then
Cells(i, 5).Font.ColorIndex = 3
Cells(i, 5).Font.Underline = xlUnderlineStyleDouble
End If
Next
End Sub
I hope that this helps.
Cheerio,
vcoolio.
smuneeb
Posts
67
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
1
Nov 30, 2015 at 03:55 AM
Nov 30, 2015 at 03:55 AM
Hi vcoolio
It works but only if each and every time it has to run the Macro button by clicking it.
Can it be make much more better Or in other words it execute on entering the required request which was previously posted.
Thanks
It works but only if each and every time it has to run the Macro button by clicking it.
Can it be make much more better Or in other words it execute on entering the required request which was previously posted.
Thanks
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Nov 30, 2015 at 09:12 PM
Nov 30, 2015 at 09:12 PM
Hello Smuneeb,
I just read through your posts again a couple of times and I thought that perhaps you were wanting a Worksheet_Change event instead. Just in case you did want this, then the following code should suffice:-
Each time you enter a criteria ("A" or "Zero") into any cell in Column E, the formatting should appear.
The code needs to go into the work sheet module, so right click on the sheet tab and select "view code". In the big white field that appears, paste the above code.
Just another option for you.
Following is a link to my updated test work book:-
https://www.dropbox.com/s/dp0gcfy05cvpy50/Smuneeb2%28Cond.Format%20Font%20and%20Underline%2CWorkSheet_Change%20event%29.xlsm?dl=0
Cheerio,
vcoolio.
I just read through your posts again a couple of times and I thought that perhaps you were wanting a Worksheet_Change event instead. Just in case you did want this, then the following code should suffice:-
Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.EnableEvents = False Application.DisplayAlerts = False If Target.Column = 5 Then cell = Target End If If cell = "A" Or cell = "Zero" Then Target.Font.ColorIndex = 3 Target.Font.Underline = xlUnderlineStyleDouble End If Application.ScreenUpdating = True Application.EnableEvents = True Application.DisplayAlerts = True End Sub
Each time you enter a criteria ("A" or "Zero") into any cell in Column E, the formatting should appear.
The code needs to go into the work sheet module, so right click on the sheet tab and select "view code". In the big white field that appears, paste the above code.
Just another option for you.
Following is a link to my updated test work book:-
https://www.dropbox.com/s/dp0gcfy05cvpy50/Smuneeb2%28Cond.Format%20Font%20and%20Underline%2CWorkSheet_Change%20event%29.xlsm?dl=0
Cheerio,
vcoolio.
smuneeb
Posts
67
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
1
Dec 3, 2015 at 04:06 AM
Dec 3, 2015 at 04:06 AM
Hi vcoolio
Thanks for another support
this is also good but their is one issue I have got is that
I would like to started from Row 11 also
In this code it started from Row 1
Thanks again for your cooperation
Thanks for another support
this is also good but their is one issue I have got is that
I would like to started from Row 11 also
In this code it started from Row 1
Thanks again for your cooperation
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
>
smuneeb
Posts
67
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
Dec 3, 2015 at 05:19 AM
Dec 3, 2015 at 05:19 AM
Hello Smuneeb,
The whole column is active, so you can start wherever you want in the column. If you have headings/titles etc. down to row 10, then just start in row 11.
Cheerio,
vcoolio.
The whole column is active, so you can start wherever you want in the column. If you have headings/titles etc. down to row 10, then just start in row 11.
Cheerio,
vcoolio.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
>
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
Dec 3, 2015 at 06:31 AM
Dec 3, 2015 at 06:31 AM
Or, if you don't want anything active above row 11, then the following should work also:-
https://www.dropbox.com/s/5yreze3bmx1ynjl/Smuneeb3%28Cond.Format%20Font%20and%20Underline%2CWorkSheet_Change%20event%29.xlsm?dl=0
Cheerio,
vcoolio.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
If Intersect(Target, Range("E11:E" & Rows.Count)) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
If Target.Value = "A" Or Target.Value = "Zero" Then
Target.Font.ColorIndex = 3
Target.Font.Underline = xlUnderlineStyleDouble
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
https://www.dropbox.com/s/5yreze3bmx1ynjl/Smuneeb3%28Cond.Format%20Font%20and%20Underline%2CWorkSheet_Change%20event%29.xlsm?dl=0
Cheerio,
vcoolio.
Nov 30, 2015 at 06:00 AM
Thanks it worked perfectly
Nov 30, 2015 at 06:28 AM
Cheerio,
vcoolio.