Newbie Requiring VBA Assistance

[mattpope697]

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

 

[6StringJazzer]

I'm unclear as to why you need to remove conditional formatting and then add it back. If you set it up for the entire column, instead of starting in row 2, then you could just leave it alone. Your CF rules do not involve array formulas so this should not be a performance hit.

 

[mattpope697]

I'm unclear as to why you need to remove conditional formatting and then add it back. If you set it up for the entire column, instead of starting in row 2, then you could just leave it alone. Your CF rules do not involve array formulas so this should not be a performance hit.

Hi,

Many thanks for replying. The standard conditional formatting isn't working after the table is adjusted. The table has been created to import data from a SQL database dependent on a location, which is why the number of rows and columns varies.

I did actually miss a step in my original question. I also need to delete any column that has 'column*' in row 1.

If you could assist, it would be gratefully appreciated as I think it may take me many years for me to learn the coding required to fix this issue.

Many thanks

Matt