Conditional formatting within code

[Datagirl8472]

Hello

Is it possible to enter conditional formatting via the code rather than selecting each cell individually?

For example, for cell D3 I need to enter conditional formatting is formula of =isna(match(long,"worksheetname!$d$1:$d$1200,0))
And the same for k3, but searching column k instead
And for r3 etc

Then for cell G4 for example, instead of searching for "long" I need to search for "big" and this is then repeated four times over four different columns.

Any ideas? I have at least 60 X 4 occurrences to do..... Code would be far easier ...... If I knew how to do it!!!!

[Aardigspook]

For the first part of your question, use a relative column reference instead of absolute and you can just copy the formatting between columns. i.e. use d$1:d$1200

For the second part:
If the long/big/etc is changing on each row, then you could put those words into another column and use OFFSET with COLUMN.
For example:

A3 - long
A4 - big
A5 - short

D3 formula:

Please Login or Register  to view this content.

Drag this down and you'll get:

D3 - long
D4 - big
D5 - short

Drag it right and you'll get the same in E3/4/5.

You can then put this (amended suitably depending on where you put the words) into your larger formula.

Does that help?

[Datagirl8472]

That may just do it.......let me have a play! I'm still learning!!

Daft question..,.within the formula for formatting, =isna(match("long",worksheetname!$d$1:$d$1200,0)) where do I actually put that section for the offset within the formula. Thanks so much

[Aardigspook]

It would replace "long", so something like this:
=isna(match(OFFSET((D3),0,-(COLUMN()-1)),worksheetname!d$1:d$1200,0))

[Datagirl8472]

Amazing thank you x

[Aardigspook]

You're welcome.

If the above has answered your question, then please mark this thread as [Solved] (instructions in my signature), so that other members/visitors can see that there's a solution now. Thank you.

If instead you have any follow-up questions, just let us know