Conditional formatting in VBA

[jeffreybrown]

Hi,

Must admit the R1C1 still confuses me, but somehow I have this working. Currently the RC4 displays in conditional format as $D8 and the RC4 as $C8, but I'm confused at how it knows to start at R8? Is there any way to make the formulas read ="=$D8=""No""" instead?

Please Login or Register  to view this content.

[DonkeyOte]

R1C1 is I think quite straightforward once you get the hang of the basic premise that the referencing is relative to where the formula etc is being placed...

eg if cell were say A10 then

RC is A10

R[1]C is A11

R[1]C[1] is B11

R20C is A$20

R20C2 is $B$20

so ...

-- anything appearing post R/C in [ ] is a relative adjustment
(where negative for R equates to upwards and for C equates to left)

-- anything appearing post R/C outside of brackets is an absolute reference

-- if R/C is not followed by a number it adopts relative position

For column references (as opposed to individual cells) then again using A10 as the "base"

C is A:A

C1 is $A:$A

C[3] is D:D

and for row references

R is 10:10

R1 is $11:$11

R[3] is 13:13

I hope that helps.

[jeffreybrown]

That does help a lot Luke. I think you are right, once you can come to grips with understanding how R1C1 works it is pretty straightforward. For my problem I finally came up with =AND(LEN(RC[-1])>0,ISBLANK(RC)) which in normal referencing is =AND(LEN(C8)>0,ISBLANK(D8)). I finally realized with starting in D8 I then adjust from that starting point. This seems to work as I paste the format through column G. Again, thanks for the push in the right direction.