Add conditional formatting and loop through activesheet

Hi Folks,

I have the following code that adds some formatting to the active sheet via a macro. It compares data in column B<>E and applies the result to column G.

Is there a way to repeat this process every 3 columns until the last used column. e.g. compare E<>H and applies it to column J
compare H<>K and applies it to column M and so on until last column.

Please Login or Register  to view this content.

Many thanks
Andy

Possibly...

Please Login or Register  to view this content.

Hi dangelor,

I have noticed that when my data gets to column AE the conditional formatting stops and I get an error

Runtime error '5':

Invalid procedure call or argument.

Please Login or Register  to view this content.

I am a little unsure of what the code is doing but have tried changing the number 64 to 84 and it does extend the range but earlier columns lose their formatting.

Hope you can assist

Seems columns with 2 letters broke the code. Try this instead...

Please Login or Register  to view this content.

Hi dangler,

That has done the trick. Thank you so very much.

I need to do a lot of learning, but with people like you providing invaluable help means I can try and work out how you did it and hopefully learn

That's what I did... Good luck!

Hi dangelor,

Sorry to ask but if possible could you point me in the right direction with regards adding another formula to existing code.
I have modded your code but keep getting the error "invalid procedure or call argument"


The additional formula i have tried to add is
=AND(E22=B22,G22<D22/1.2)

I have added;
Dim s3 As String
Dim s4 As String

s3 = Replace(rg.Offset(, 0).Address, "$", "")
s4 = Replace(rg.Offset(, -3).Address, "$", "")

and also this line of code
Formula1:="=" & "AND" & "(" & s1 & "=" & s2 & "," & s3 & "<" & s4 & "/" & "1.2")

Please Login or Register  to view this content.

It's a little too complicated for me but with a bit of help hopefully I can get a better understanding
Thanks for any help received