How to make a macro work in selected worksheets and how to fix a macro error from CF

[k0st4din]

Hello, everyone,
I searched a lot on the internet, finally I made 3 macros with clicks, for my needs.
However, then when I tried to see if they would work, I got an error. After that, I started reading on the Internet again and it turned out that if I wanted to use conditional formatting, the macros themselves were written a little differently.
I wrote and asked another site for help, but it's been 1 week now with no response and I'm a bit desperate.
The macros are three, with conditions that are in them, but since I need them for 30 worksheets, I'm trying to add an Array so that these 3 conditional formatting can be done in all of them and I don't have to click on so many times and for each worksheet.
I am asking for some help with adding (if at all possible) to do it on more worksheets and the error that stops the selection to be bolted and italic.
The idea is that these are 30 - 1000% identical ranges of tables and the macro notes which cells should change under the given conditions.
I will attach the macros and sample table for testing, but if there is anything that is not clear I remain available to help.

Sub test2BLACK()


Application.ScreenUpdating = False
Range("O62:Z62").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=N62<O62"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.Pattern = xlNone
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub





Sub table()
Application.ScreenUpdating = False
Range("O3:Z60").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=O3=MAX($O3:$Z3)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(O3))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.Pattern = xlNone
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub





Sub TEST3RED()
Application.ScreenUpdating = True
Range("P62:Z62").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=O62>P62"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.Color = vbRed '-16776961
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.Pattern = xlNone
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = False
End Sub




3037630375

[k0st4din]

Hello,
Does anyone have any idea how this conditional formatting can be implemented in the macro?
I just don't know how to do it. Continuing to search the Internet, I have shown changes, but still things do not work.
In my case it's .Bold , some say it should be .Font .Bold - but it still won't.
Many thanks once again.

[Bob Phillips]

As far as I can see, your code works fine in principle. I had to change the ranges to suit the data, and I tidied the code a bit, but basically it is your code, no error


Sub TEST3RED()
Dim i As Long

Application.ScreenUpdating = True

With Range("A62:N62")

For i = .FormatConditions.Count To 1 Step -1

.FormatConditions(i).Delete
Next i

.FormatConditions.Add Type:=xlExpression, Formula1:="=A62>B62"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Font
.Bold = True
.Italic = True
.Color = vbRed '-16776961
.TintAndShade = 0
End With
With .FormatConditions(1).Interior
.Pattern = xlNone
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
End With

Application.ScreenUpdating = False
End Sub

[georgiboy]

I am asking for some help with adding (if at all possible) to do it on more worksheets and the error that stops the selection to be bolted and italic.
The idea is that these are 30 - 1000% identical ranges of tables and the macro notes which cells should change under the given conditions.

A suggestion for the application of the CF to multiple worksheets might be to copy the range after the CF has been applied in one sheet and paste formats in the destination sheets. It should be easy as you describe the ranges being the same size etc...


Sub test()
Dim wsArray As Variant, ws As Worksheet

wsArray = Array("Sheet5", "Sheet6", "Sheet7") ' sheets to place the CF
Sheet1.Range("A1:A5").Copy ' original CF

For Each ws In Sheets(wsArray)
ws.Range("A1").PasteSpecial xlPasteFormats ' paste formatting only
Next ws
Application.CutCopyMode = False
End Sub

As for the error on .Bold, i don't get that error from your attachment. Did you upload a file where the error was present inside?

[k0st4din]

Hello,
I started trying again, the stupidest thing is that once it works, once it doesn't want to.
Both the first table I have attached and the one I will now upload are real and taken as copies of the real table.
What strikes me is that when I click on the selected macro and then I checked in the conditional formatting itself, if I click 3 times for example black and bolted, there appear 3 times these button presses.
And this condition: Stop if true - although it is there as a condition in the macro, it does not execute it.
I haven't taken a picture now, but the previous one shown in the 1st comment is this same error. And here, what doesn't make sense to me is that if I pressed black, it gives the error of red, the opposite is also true.30393

[k0st4din]

A suggestion for the application of the CF to multiple worksheets might be to copy the range after the CF has been applied in one sheet and paste formats in the destination sheets. It should be easy as you describe the ranges being the same size etc...


I want to add because I'm concerned that I may have been misunderstood: 1000% is the rows and columns, but the values in each worksheet, and this coloring, will be different in each worksheet.
Again, I did a lot of reading and clicks to try to do something that I clearly couldn't do. :)
My idea is that by pressing Black it will cycle through all the worksheets and execute it. Then I press Red and the same again. At the end, I press the table macro and it performs coloring according to the search criteria.

[Bob Phillips]

I think your problem is not clearing out previous conditions


Sub test2BLACK()
Dim i As Long

Application.ScreenUpdating = False

With Range("O62:Z62")

For i = .FormatConditions.Count To 1 Step -1

.FormatConditions(i).Delete
Next i

.FormatConditions.Add Type:=xlExpression, Formula1:="=N62<O62"
.FormatConditions(.FormatConditions.Count).SetFirstPriority

With .FormatConditions(1)

With .Font

.Bold = True
.Italic = True
.TintAndShade = 0
End With

With .Interior

.Pattern = xlNone
.TintAndShade = 0
End With

.StopIfTrue = False
End With
End With

Application.ScreenUpdating = True
End Sub

Sub table()
Dim i As Long

Application.ScreenUpdating = False

With Range("O3:Z60")

For i = .FormatConditions.Count To 1 Step -1

.FormatConditions(i).Delete
Next i

.FormatConditions.Add Type:=xlExpression, _
Formula1:="=O3=MAX($O3:$Z3)"
.FormatConditions(.FormatConditions.Count).SetFirstPriority

With .FormatConditions(1)

With .Font

.Bold = True
.Italic = True
.TintAndShade = 0
End With

With .Interior

.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.599963377788629
End With

.StopIfTrue = False
End With

.FormatConditions.Add Type:=xlExpression, _
Formula1:="=LEN(TRIM(O3))=0"
.FormatConditions(.FormatConditions.Count).SetFirstPriority

With .FormatConditions(1).Interior

.Pattern = xlNone
.TintAndShade = 0
End With

.FormatConditions(1).StopIfTrue = False
End With

Application.ScreenUpdating = True
End Sub

Sub TEST33RED()
Dim i As Long

Application.ScreenUpdating = True

With Range("p62:Z62")

For i = .FormatConditions.Count To 1 Step -1

.FormatConditions(i).Delete
Next i

.FormatConditions.Add Type:=xlExpression, Formula1:="=O62>P62"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Font
.Bold = True
.Italic = True
.Color = vbRed '-16776961
.TintAndShade = 0
End With
With .FormatConditions(1).Interior
.Pattern = xlNone
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
End With

Application.ScreenUpdating = False
End Sub

[k0st4din]

Hi Bob Phillips (http://www.vbaexpress.com/forum/member.php?2139-Bob-Phillips)
I don't think that's where the problem comes from, because before writing, I personally deleted all conditionals.
But to make sure it wasn't my fault, I opened a brand new table, just copied the data and pasted it into it.
After that, I took your slightly modified macros and placed them.
I started trying them one by one and the result is a total mess.
I will attach pictures of all the steps:
macro table - turns green, but first incorrect cells (I feel like it's random), does not remove the requirement If true, to stop.
As for the black and red, you'll be able to see for yourself that I'm putting cell O1048564 - I have no idea, after the macro says what the range is, why does it compare to ranges and cells that aren't searched at all?
Is it that different when working with conditional formatting in a macro environment?
I have already despaired.
In a macro environment, it doesn't want to work, but when I do it by hand, things work out, and the conditions are not too difficult!?
I will upload the pictures.
Thanks in advance!

[Bob Phillips]

Maybe you are a victim of the CF relative positioning, exacerbated if you select cells.

See if this works better


Option Explicit

Sub test2BLACK()
Dim i As Long

Application.ScreenUpdating = False

With Range("O62:Z62")

.Cells(1, 1).Activate

For i = .FormatConditions.Count To 1 Step -1

.FormatConditions(i).Delete
Next i

.FormatConditions.Add Type:=xlExpression, Formula1:="=N62<O62"
.FormatConditions(.FormatConditions.Count).SetFirstPriority

With .FormatConditions(1)

With .Font

.Bold = True
.Italic = True
.TintAndShade = 0
End With

With .Interior

.Pattern = xlNone
.TintAndShade = 0
End With

.StopIfTrue = False
End With
End With

Application.ScreenUpdating = True
End Sub

Sub table()
Dim i As Long

Application.ScreenUpdating = False

With Range("O3:Z60")

.Cells(1, 1).Activate

For i = .FormatConditions.Count To 1 Step -1

.FormatConditions(i).Delete
Next i

.FormatConditions.Add Type:=xlExpression, _
Formula1:="=O3=MAX($O3:$Z3)"
.FormatConditions(.FormatConditions.Count).SetFirstPriority

With .FormatConditions(1)

With .Font

.Bold = True
.Italic = True
.TintAndShade = 0
End With

With .Interior

.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.599963377788629
End With

.StopIfTrue = False
End With

.FormatConditions.Add Type:=xlExpression, _
Formula1:="=LEN(TRIM(O3))=0"
.FormatConditions(.FormatConditions.Count).SetFirstPriority

With .FormatConditions(1).Interior

.Pattern = xlNone
.TintAndShade = 0
End With

.FormatConditions(1).StopIfTrue = False
End With

Application.ScreenUpdating = True
End Sub

Sub TEST33RED()
Dim i As Long

Application.ScreenUpdating = True

With Range("P62:Z62")

.Cells(1, 1).Activate

For i = .FormatConditions.Count To 1 Step -1

.FormatConditions(i).Delete
Next i

.FormatConditions.Add Type:=xlExpression, Formula1:="=O62>P62"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Font
.Bold = True
.Italic = True
.Color = vbRed '-16776961
.TintAndShade = 0
End With
With .FormatConditions(1).Interior
.Pattern = xlNone
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
End With

Application.ScreenUpdating = False
End Sub