mattpope697
New Member
- Joined
- Jun 24, 2022
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
- Mobile
Hi All,
I am a complete novice to VBA and have just completed a basic course of VBA, huge thanks to #thespreadsheetguru. However, this little knowledge although great and has certainly fueled me for further information courses, has not really helped me, thus far, in solving my issues.
I have been scouring the net for solutions to my Excel issue and found several pieces of coding that I have adapted to meet my needs and pieced them together to form the following, which works but...
'Delete conditional formatting'
Dim colx As Long
Dim H As Worksheet
Set H = Sheet1
For colx = 6 To 8 Step 2 'needs to be dynamic'
'Insert the Column'
Call H.Columns(colx).Insert(Shift:=xlToRight)
'Put the formula in the new Column'
H.Range(H.Cells(2, colx), H.Cells(2, colx)).Select
Selection.FormulaArray = _
"=INDEX(Table_ExternalData_14[TNA Required],MATCH(RC1&R1C[-1],Table_ExternalData_14[PersonnelShortName]&Table_ExternalData_14[ModelShortName],0))"
'hide column'
Selection.EntireColumn.Hidden = True
'add conditional formatting'
Next colx
End Sub
The problem I now have is that I would first (before the above code) like to remove all conditional formatting to the dynamic range ($E$2:last cell). I am not sure how to achieve this, as both rows and columns are continually being added and I'm guessing this adjustment would also be required in the above, to make it accurate??
I would then (after or before hiding the columns) like to conditional format the dynamic range ($E$2:last cell) with the following, as the normal conditional formating does not work when rows and columns are added?
=AND(E2=0,(F2="None")) - Fill RGB 189, 215, 238
=AND(G2=0,(H2<Settings!$B$5)) - Fill RGB 189, 215, 238
=AND(G2=0, (H2="User")) - Fill RGB 228, 98, 98
Cell value is between Settings!$B$5 and Settings!$B$6 - Fill RGB 255, 230, 153
=>Settings!$B$6 - Fill RGB 198, 224, 180
Many thanks in advance
Matt
I am a complete novice to VBA and have just completed a basic course of VBA, huge thanks to #thespreadsheetguru. However, this little knowledge although great and has certainly fueled me for further information courses, has not really helped me, thus far, in solving my issues.
I have been scouring the net for solutions to my Excel issue and found several pieces of coding that I have adapted to meet my needs and pieced them together to form the following, which works but...
'Delete conditional formatting'
Dim colx As Long
Dim H As Worksheet
Set H = Sheet1
For colx = 6 To 8 Step 2 'needs to be dynamic'
'Insert the Column'
Call H.Columns(colx).Insert(Shift:=xlToRight)
'Put the formula in the new Column'
H.Range(H.Cells(2, colx), H.Cells(2, colx)).Select
Selection.FormulaArray = _
"=INDEX(Table_ExternalData_14[TNA Required],MATCH(RC1&R1C[-1],Table_ExternalData_14[PersonnelShortName]&Table_ExternalData_14[ModelShortName],0))"
'hide column'
Selection.EntireColumn.Hidden = True
'add conditional formatting'
Next colx
End Sub
The problem I now have is that I would first (before the above code) like to remove all conditional formatting to the dynamic range ($E$2:last cell). I am not sure how to achieve this, as both rows and columns are continually being added and I'm guessing this adjustment would also be required in the above, to make it accurate??
I would then (after or before hiding the columns) like to conditional format the dynamic range ($E$2:last cell) with the following, as the normal conditional formating does not work when rows and columns are added?
=AND(E2=0,(F2="None")) - Fill RGB 189, 215, 238
=AND(G2=0,(H2<Settings!$B$5)) - Fill RGB 189, 215, 238
=AND(G2=0, (H2="User")) - Fill RGB 228, 98, 98
Cell value is between Settings!$B$5 and Settings!$B$6 - Fill RGB 255, 230, 153
=>Settings!$B$6 - Fill RGB 198, 224, 180
Many thanks in advance
Matt