Conditional Formatting through Macro [Solved/Closed]

Report
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
-
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
-
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

Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
1
Hi

Thanks it worked perfectly
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230 >
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017

Excellent! Glad that I could help.

Cheerio,
vcoolio.
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
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.
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
1
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
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
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.
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
1
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
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230 >
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017

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.
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230 >
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021

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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!