View Full Version : How to make a macro work in selected worksheets and how to fix a macro error from CF
k0st4din
12-12-2022, 11:15 PM
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
12-15-2022, 10:20 PM
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
12-16-2022, 03:46 AM
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
12-16-2022, 06:29 AM
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
12-17-2022, 12:26 AM
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
12-17-2022, 12:41 AM
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
12-18-2022, 09:52 AM
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
12-18-2022, 10:48 PM
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
12-19-2022, 12:13 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.