Pause the macro and let the user select a color

Question

Is it possible to stop this macro when opening the dialog box for filling format cells so that I can select a color and continue the script when OK is selected.

Sub StripesOdd() Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=MOD(ROW(),2)=1" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorDark1 .TintAndShade = -0.14996795556505 End With Selection.FormatConditions(1).StopIfTrue = False Application.Goto Reference:="StripesOdd" End Sub 

Answer 1

Yes it is possible. Before you go this far too far - VBA is an automation tool. If I were you, I would spend more time adjusting the colors based on the data (i.e., if the field is “x”, then the color is blue), and does not allow the user to do this randomly. This is not automation if manual intervention is required.

You need to insert user input into the code, which requires the user to make a choice before the codes continue.

This is found at http://www.cpearson.com/Excel/Colors.aspx This requires the base modColorFunctions file

Display color picker dialog

The modColorFunctions module contains a function called SelectColorDialog, which displays the Windows color picker and returns the value to RGB Long color. If the user cancels the dialog, the result is -1. For instance,

 Dim RGBColor As Long Dim Default As Long Default = RGB(255, 0, 255) 'default to purple RGBColor = ChooseColorDialog(DefaultColor:=Default) If RGBColor < 0 Then Debug.Print "*** USER CANCELLED" Else Debug.Print "Choice: " & Hex(RGBColor) End If 

You will need to insert the call into SelectColorDialog when you want the user to enter color.