VBA Data Validation with Conditional Formatting

[CaptainKen]

Hope someone can please help.

1. The Conditional Formatting code below works on this one sheet with Sheet Protection Off. However, with Sheet Protection On, it generates "Run-time error1004: Application defined or object-defined error".

Please see this video for details. Note that I neglected to point out in the video that I get the error on ANY cell in sheet I click on.
Video detailing the issues

2. It would also be appreciated if the code would work with multiple ranges (G5:G9, G11:G13, G17:G26, etc) which are exactly the same across the 11 month (accept "Jan") tabs.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Definining the variables:
  Dim Rng01 As Range
  Dim condition As FormatCondition

'Setting the range on which conditional formatting is to be desired
  Set Rng01 = Range("G5:G9")

'To delete/clear any existing conditional formatting from the range
   Rng01.FormatConditions.Delete

'Setting the criteria for Data Validation
   Set condition = Rng01.FormatConditions.Add(Type:=xlExpression, Formula1:="=$E5<>""Variable""")

'Preferred Data Validation approach that doesn't work
'    With Rng01.Validation
'        .Delete
'        .Add Type:=xlValidateCustom, _
'            AlertStyle:=xlValidAlertStop, _
'            Formula1:="=$E5<>"Variable""
'        .ErrorTitle = "Error Title Here"
'        .ErrorMessage = "Error Message Here"
'    End With
   
    'Conditional Formating to set the background Budget cell color
   With condition
        With Rng01.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
        End With
    End With

'Get cell color
    'Manually Select a cell, choose a color from toolbar, then click out of cell for Color number to display
    'Range("J5:J5") = Range("J5:J5").Interior.color

End Sub

Monthly Budget Tool BEFORE Retirement - TESTING.xlsm
	A	B	C	D	E	F	G	H
1
2		MONTHLY BUDGET
3
4			CATEGORY	DESCRIPTION	TYPE	ACTUAL	BUDGET	OVER / (UNDER)
5		INCOME	Paycheck (After-Tax)	Pay Check	Variable		-	-
6				Other	Variable		-	-
7				0	Fixed		-	-
8				0	0		-	-
9				0	0		-	-
10				Subtotal		-	-	-
11			Additional Income	Bonuses, Tips, Commissions, Miscellaneous Sales, Etc.	Variable		-	-
12				Savings Interest	Variable		-	-
13				Credit Card Cash Back	Variable		-	-
14				Subtotal		-	-	-
15			TOTAL INCOME			-	-	-
16
17		EXPENSES	Home	Rent/Mortgage	Fixed		-	-
18				Home Insurance	Fixed		-	-
19				HOA	Fixed		-	-
20				Electric	Variable		-	-
21				Gas	Variable		-	-
22				Water & Garbage	Variable		-	-
23				Internet	Variable		-	-
24				Personal Property or Other Taxes	Variable		-	-
25				Phone	Fixed		-	-
26				Home Furnishings, Repairs	Variable		-	-
27				Subtotal		-	-	-
FEB

Cell Formulas
Range	Formula
C5,G17:G26,D17:E26,C17,D12:E13,C11:D11,D8:E9,D5:D7,G5:G9,G11:G13	C5	=JAN!C5
H5:H9,H11:H13,H17:H26	H5	=G5-F5
F10:H10	G10	=SUM(G5:G9)
F14:H14	F14	=SUM(F11:F13)
F15:H15	F15	=SUM(F14,F10)
F27:H27	F27	=SUM(F17:F26)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G5:G9	Expression	=$E5<>"Variable"	text	YES

Cells with Data Validation
Cell	Allow	Criteria
E5:E27	List	Fixed, Variable

 

[Tupe77]

My understanding is that you need to unprotect the sheet before you can define the formatting.

However, I don't understand why formatting must be changed every time the sheet selection changes (Worksheet_SelectionChange), because conditional formatting automatically reacts to changes in the target area.

I think adding conditional formatting should be done once and without the Worksheet_SelectionChange event.

This is just an example, but it can be modified if conditional formatting can be implemented in your workbook like this?

Sub AddConditionalFormating()
Worksheets("FEB").Unprotect "Your password here"
'Definining the variables:
  Dim Rng01 As Range
  Dim condition As FormatCondition

'Setting the range on which conditional formatting is to be desired
  Set Rng01 = Range("G5:G9")

'To delete/clear any existing conditional formatting from the range
   Rng01.FormatConditions.Delete

'Setting the criteria for Data Validation
   Set condition = Rng01.FormatConditions.Add(Type:=xlExpression, Formula1:="=$E5<>""Variable""")

'Preferred Data Validation approach that doesn't work
'    With Rng01.Validation
'        .Delete
'        .Add Type:=xlValidateCustom, _
'            AlertStyle:=xlValidAlertStop, _
'            Formula1:="=$E5<>"Variable""
'        .ErrorTitle = "Error Title Here"
'        .ErrorMessage = "Error Message Here"
'    End With
   
    'Conditional Formating to set the background Budget cell color
   With condition
        With Rng01.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
        End With
    End With

'Get cell color
    'Manually Select a cell, choose a color from toolbar, then click out of cell for Color number to display
    'Range("J5:J5") = Range("J5:J5").Interior.color
    Worksheets("FEB").Protect "Your password here"
End Sub

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

 

[CaptainKen]

Great that did it thank you.

Would you mind also tackling my other issue mentioned?

1) How to I convert this single line of code

Set condition = Rng01.FormatConditions.Add(Type:=xlExpression, Formula1:="=$E5<>""Variable""")

Into getting this approach to work?

'Preferred Data Validation approach that doesn't work
'    With Rng01.Validation
'        .Delete
'        .Add Type:=xlValidateCustom, _
'            AlertStyle:=xlValidAlertStop, _
'            Formula1:="=$E5<>"Variable""
'        .ErrorTitle = "Error Title Here"
'        .ErrorMessage = "Error Message Here"
'    End With

2) How do I get everything to be applied to multiple ranges such as all the Feb-Dec sheets?

 

[CaptainKen]

Oops I forgot to include these details:

These are the ranges:
Range("G5:G9,G11:G17,G21:G32,G34:G41,G43:G49,G51:G63,G65:G70,G72:G82,G86:G93")

These are the formula values for each respective range:
=$E5<>"Variable"
=$E11<>"Variable"
=$E21<>"Variable"
=$E34<>"Variable"
=$E43<>"Variable"
=$E51<>"Variable"
=$E65<>"Variable"
=$E72<>"Variable"
=$E86<>"Variable"

 

[Tupe77]

I have a small doubt that your intention is not to add to the range Range("G5:G9") Data Validation with the rule Formula1:="=$E5<>"Variable""

Formula1:="=$E5<>"Variable"" means that all other input to the cell is allowed except the text "Variable"

What do you want Data Validation to do?
Maybe it's a list to choose Fixed or Variable?
Or something else?

 

[CaptainKen]

Yes it is based on the drop down list in each respective section as demo'd in the video.

Sorry, here's the correct list, the other was for Conditional Formatting I was doing:
=$E5="Variable"
=$E11="Variable"
=$E21="Variable"
=$E34="Variable"
=$E43="Variable"
=$E51="Variable"
=$E65="Variable"
=$E72="Variable"
=$E86="Variable"