Conditional Formatting comparing 2 clmns - one is deleted, setting new refernce clmn

[kyrgyzstanart]

Hi all,

Here is a challenge I am facing.

1. Two Columns w/ Integers - Column "A" and Column "B".
2. Conditional formatting testing if Values in Column "A" are less than Values in Column "B"
3. If the condition is true then a cell in the column "A" (Font = Bold, Text Color = WHITE, Cell Color = RED). Otherwise initial formatting is kept.

I did this via standard Excel functionality - Conditional formatting, and not via macros, because the Values in the Columns can change dynamically (updated by user), therefore I want the conditional formatting applied right away after the change is made.

So here is the problem:

In the beginning of every month I need to delete Column "B", Column "C" becomes Column "B". However, the conditional formatting "looses" the refernce area (which was column "B"), and gives "#REF!".

I would like to create a macro that would modify the rule after deleting Column "B", and change the "#REF!" to the "new" Column "B" (formerly Column "C").

I do it w/ the following code:

Please Login or Register  to view this content.

However, I would like the user to tell Excel via the InputBox Function which column to use as reference for comparing to column "A". And then modify the conditional formatting accordingly (to reference to the user defined column).

When I put the following code:

Please Login or Register  to view this content.

The conditional formatting is done based on Values in the "R" Range, but not the the reference to the cells in "R" Range... Hence the conditional formatting is no longer dynamic because if Values in "R" Range are changed then you need to re-run the macro. But if I could make Excel take "R" as Range and not Values from "R" for the conditional formatting, it would have been perfect.