Conditional Formatting through Macro

Solved/Closed
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 - Nov 29, 2015 at 02:28 AM
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - Dec 3, 2015 at 06:31 AM
Hello,

I would like to request that how Conditional Formatting would apply through Macro
and the Conditions are

Range is E11 to E510
if Column contains character "A" or word "Zero"
then font color changes to Red and have double underline.

Thanks

3 replies

vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
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.
2
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1
Nov 30, 2015 at 06:00 AM
Hi

Thanks it worked perfectly
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252 > smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017
Nov 30, 2015 at 06:28 AM
Excellent! Glad that I could help.

Cheerio,
vcoolio.
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Nov 30, 2015 at 01:19 AM
Hello Smuneeb,

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.
1
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1
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
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
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:-


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.
0
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1
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
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252 > smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017
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.
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252 > vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022
Dec 3, 2015 at 06:31 AM
Or, if you don't want anything active above row 11, then the following should work also:-


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.
0