The below macro enters the following formula into row 5 of the chosen formula column.

=IF(A5<>SUM(E5:G5);"Caution!";"ok") 'see line 6 of the code

The second inputbox prompts the user to enter another column letter which determines the number of rows the formula is copied down.

The macro then enters the formulas into all worksheets with the exception of a couple of them.

NOW, I also would like to apply conditional formatting to the entered formulas, i.e.

All Cells which have been filled with the term 'Caution!" should get a fill RGB 220, 17, 17 coupled with a white font

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Sub Enter_Formula_Multiple_Worksheets()Dim ws As WorksheetDim rng As RangeDim lr As Long, Col As LongDim Formula As StringFormula = "IF(A5<>SUM(E5:G5),""Caution!"",""ok"")"Dim ColNameFormula As StringDim ColNameCopyingDown As StringColNameFormula = InputBox("Choose column letter where the formula will be entered", "Set Column Letter for formula")If ColNameFormula = "" ThenMsgBox "You didn't select a column letter.", vbExclamationExit SubEnd IfColNameCopyingDown = InputBox("Choose column letter to determine the number of copying down actions of the formula", "Set Column Letter")If ColNameCopyingDown = "" ThenMsgBox "You didn't select a column letter.", vbExclamationExit SubEnd IfApplication.ScreenUpdating = FalseFor Each ws In Worksheets Select Case ws.Name Case "1_Index", "2_Auswertung", "3_Gesamtliste", "X_Sorting", "Y_ColumnHeader", "Z_Requirements" Case Else lr = ws.Cells(Rows.Count, Range(ColNameCopyingDown & 1).Column).End(xlUp).Row If lr > 4 Then ws.Range(ws.Cells(5, Range(ColNameFormula & 1).Column), ws.Cells(lr, Range(ColNameFormula & 1).Column)).Formula = "=" & Formula End If End Select lr = 0Next wsApplication.ScreenUpdating = TrueEnd Sub

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sub Enter_Formula_Multiple_Worksheets()Dim ws As WorksheetDim rng As RangeDim lr As Long, Col As Long, cnt As LongDim Formula As StringFormula = "IF(A5<>SUM(E5:G5),""Caution!"",""ok"")"Dim ColNameFormula As StringDim ColNameCopyingDown As StringColNameFormula = InputBox("Choose column letter where the formula will be entered", "Set Column Letter for formula")If ColNameFormula = "" ThenMsgBox "You didn't select a column letter.", vbExclamationExit SubEnd IfColNameCopyingDown = InputBox("Choose column letter to determine the number of copying down actions of the formula", "Set Column Letter")If ColNameCopyingDown = "" ThenMsgBox "You didn't select a column letter.", vbExclamationExit SubEnd IfApplication.ScreenUpdating = FalseFor Each ws In Worksheets Select Case ws.Name Case "1_Index", "2_Auswertung", "3_Gesamtliste", "X_Sorting", "Y_ColumnHeader", "Z_Requirements" Case Else lr = ws.Cells(Rows.Count, Range(ColNameCopyingDown & 1).Column).End(xlUp).Row If lr > 4 Then Set rng = ws.Range(ws.Cells(5, Range(ColNameFormula & 1).Column), ws.Cells(lr, Range(ColNameFormula & 1).Column)) cnt = ws.Cells.FormatConditions.Count ws.Range(ws.Cells(5, Range(ColNameFormula & 1).Column), ws.Cells(lr, Range(ColNameFormula & 1).Column)).Formula = "=" & Formula With rng .FormatConditions.Add Type:=xlTextString, String:="Caution!", TextOperator:=xlContains .FormatConditions(cnt + 1).Font.Color = vbWhite .FormatConditions(cnt + 1).Interior.Color = RGB(220, 17, 17) End With End If End Select lr = 0Next wsApplication.ScreenUpdating = TrueEnd Sub

Sub Enter_Formula_Multiple_Worksheets()Dim ws As WorksheetDim rng As RangeDim lr As Long, Col As LongDim Formula As StringDim ColRng As RangeFormula = "IF(A5<>SUM(E5:G5),""Caution!"",""ok"")"Dim ColNameFormula As StringDim ColNameCopyingDown As StringColNameFormula = InputBox("Choose column letter where the formula will be entered", "Set Column Letter for formula")If ColNameFormula = "" ThenMsgBox "You didn't select a column letter.", vbExclamationExit SubEnd IfColNameCopyingDown = InputBox("Choose column letter to determine the number of copying down actions of the formula", "Set Column Letter")If ColNameCopyingDown = "" ThenMsgBox "You didn't select a column letter.", vbExclamationExit SubEnd IfApplication.ScreenUpdating = FalseFor Each ws In Worksheets Select Case ws.Name Case "1_Index", "2_Auswertung", "3_Gesamtliste", "X_Sorting", "Y_ColumnHeader", "Z_Requirements" Case Else lr = ws.Cells(Rows.Count, Range(ColNameCopyingDown & 1).Column).End(xlUp).Row If lr > 4 Then ws.Range(ws.Cells(5, Range(ColNameFormula & 1).Column), ws.Cells(lr, Range(ColNameFormula & 1).Column)).Formula = "=" & Formula End If Set ColRng = ws.Range(ws.Cells(5, Range(ColNameFormula & 1).Column), ws.Cells(lr, Range(ColNameFormula & 1).Column)) For Each cell In ColRng If cell.Value = "Caution!" Then cell.Interior.Color = RGB(220, 17, 17) cell.Font.Color = RGB(255, 255, 255) End If Next cell End Select lr = 0Next wsApplication.ScreenUpdating = TrueEnd Sub

That is not conditional formatting but applying the cell interior color and form color explicitly. And the formatting done this way will remain even if the string returned by the formula gets changed.

But you never know, asker is smart enough to tweak all the codes as per his liking as he did in his last question and accepted your answer as an accepted one though he continued with the answer provided by me. :)

Also I am curious to know how did he tweak your last solution as per his requirement. :)

0

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

thank you very much for your quick and professional help. I am afraid to tell you that your code throws an error message on column 36 (Runtime error 9, Index outside array).

As for your comments on the points awarding of my last question,

I will get back later this evening. Again, thank you very much for your great and professional help. I really appreciate it. The same applies to Shums

Sub Enter_Formula_Multiple_Worksheets()Dim ws As WorksheetDim rng As RangeDim lr As Long, Col As LongDim Formula As StringDim ColRng As RangeFormula = "IF(A5<>SUM(E5:G5),""Caution!"",""ok"")"Dim ColNameFormula As StringDim ColNameCopyingDown As StringColNameFormula = InputBox("Choose column letter where the formula will be entered", "Set Column Letter for formula")If ColNameFormula = "" ThenMsgBox "You didn't select a column letter.", vbExclamationExit SubEnd IfColNameCopyingDown = InputBox("Choose column letter to determine the number of copying down actions of the formula", "Set Column Letter")If ColNameCopyingDown = "" ThenMsgBox "You didn't select a column letter.", vbExclamationExit SubEnd IfApplication.ScreenUpdating = FalseFor Each ws In Worksheets Select Case ws.Name Case "1_Index", "2_Auswertung", "3_Gesamtliste", "X_Sorting", "Y_ColumnHeader", "Z_Requirements" Case Else lr = ws.Cells(Rows.Count, Range(ColNameCopyingDown & 1).Column).End(xlUp).Row If lr > 4 Then ws.Range(ws.Cells(5, Range(ColNameFormula & 1).Column), ws.Cells(lr, Range(ColNameFormula & 1).Column)).Formula = "=" & Formula End If Set ColRng = ws.Range(ws.Cells(5, Range(ColNameFormula & 1).Column), ws.Cells(lr, Range(ColNameFormula & 1).Column)) With ColRng .FormatConditions.Add xlTextString, String:="Caution!", TextOperator:=xlContains With .FormatConditions(.FormatConditions.Count) .SetFirstPriority With .Interior .Color = RGB(220, 17, 17) End With With .Font .Color = RGB(255, 255, 255) End With End With End With End Select lr = 0Next wsApplication.ScreenUpdating = TrueEnd Sub

You're always Welcome Andreas! No problem. Actually I learned Format Condition from Neeraj's code :)

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Neeraj, you wrote ... "But you never know, asker is smart enough to tweak all the codes as per his liking as he did in his last question and accepted your answer as an accepted one though he continued with the answer provided by me. :)"

It is always hard to award points. Shums answer on that specific question was correct and ok you are right, your code was the basis for my tweaking. Again, awarding points equitably is not an easy task. Since Shum based his correct code on yours, I will award the majority of points to you. I hope I am doing everything right.

Thank you very much, Shums and Neeraj for your great help and support. I really appreciate it.

Regards, Andreas

0

Andreas HermleTeam leaderAuthor Commented:

I now decided to share the points 50:50, hope this is ok for both of you. Regards, Andreas

0

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for freeEdge Out The Competitionfor your dream job with proven skills and certifications.Get started todayStand Outas the employee with proven skills.Start learning today for freeMove Your Career Forwardwith certification training in the latest technologies.Start your trial today

Open in new window