VBA to clear content in a range based on conditional formatting in a cell on the same row

[mathewwheeler]

I'm not very strong at VBA and the conditional formatting is based on a lookup value being greater than the value in column B. I would like the code to run anytime that the value in column B is updated.

So I'm creating a model and each row will be an employee - column B is the employee's start date (YR1 M1 (first day of the 5 year model) to YR5 M12 (last day of the model). I have each column (corresponding to a YR/M combination from YR1 M1 to YR5 M12) referencing a numeric value YR1 M1 = .0825, YR1 M2 = .165, YR1 M3 = .2475 ...

If the lookup value of column B is greater than the referenced numeric value, the cell is suppose to turn black (and the goal is to clear the data, so it does not flow through the model). With that detail, could you please help me with the structure of the VBA code? I am also open to changing the condition for formatting/clearing content if there you have any suggestions.

Thank you for any help! These forums are always very helpful in learning/improving.

 

[Marcelo Branco]

Say you have this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Employee​

[/TD]
[TD]

StartDate​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

emp1​

[/TD]
[TD]

YR1 M3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

emp2​

[/TD]
[TD]

YR2 M1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

emp3​

[/TD]
[TD]

YR2 M11​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

emp4​

[/TD]
[TD]

YR1 M7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

emp5​

[/TD]
[TD]

YR2 M2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

emp6​

[/TD]
[TD]

YR3 M7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

emp7​

[/TD]
[TD]

YR2 M2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

emp8​

[/TD]
[TD]

YR1 M8​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

emp9​

[/TD]
[TD]

YR3 M2​

[/TD]
[/TR]
</tbody>[/TABLE]


and say the Ref Number is 1.155 that corresponds to YR2 M2

Do you want to delete the rows 4, 7 and 10? Or just clear the contents of columns A and B (or only of column B)?

M.

 

[mathewwheeler]

Hi Marcelo,

Thank you for the reply. I would like to clear the content in columns C - Z based on the value in column B (and have the code run whenever the value in column B is changed).

 

[Marcelo Branco]

Hi Marcelo,

Thank you for the reply. I would like to clear the content in columns C - Z based on the value in column B (and have the code run whenever the value in column B is changed).

Questions
Where is the Ref Number entered? Which cell?
Suppose the Ref Number changes: what the code should do? Or it never changes?

M.

 

[mathewwheeler]

The reference number does not change. It is in row 5 of the spreadsheet - each column has a different reference number, because the columns represent the YR M combos.

 

[Marcelo Branco]

Row 5 of what column? Please, be specific.

hmm...I'm not understanding what you're trying to do (the purpose of the code).

You said that the code should run every time the value in column B changes. But if you are entering the start date in column B I imagine it is for a new employee and therefore does not yet have data in columns C to Z.

Or can the start date of each employee (which already exists in the database) be changed?

I'm really confused ...

Please try to explain exactly:
What data already exists;
New rows can be added? How? The Ref Numbers for those rows already exist?
In what row / column is the Ref Number
The purpose of the code.



M.

 

[mathewwheeler]

Sure - sorry for any confusion. Row 5 has the criteria for each column. The conditional format of column C is dependent on the reference number in cell C5, but the formatting of column F depends on the reference number in cell F5, etc.

The model is a forecast, so the start date can change (and most likely WILL change), so that the I can see the different scenarios, and I don't want the old #'s to pull into my model if I change the start date.

 

[Marcelo Branco]

So there are Ref Numbers in C5, D5....,Z5 and, for example, the data in column C should be cleared if column B is greater than C5. The same for column D if B > D5 and so on.
Am i right?

A small data sample, ~10 rows 5 columns (A:E), along with the expected outcome would be helpful.

M.

 

[Marcelo Branco]

An issue
Suppose in B10 is typed a date greater than C5, then cell C10 is cleared.
If, in the future, B10 is changed to a date less than C5, it will not be possible to retrieve the contents of C10.
Unless there is a static database on another sheet (untouchable) . Maybe possible, but rather complicates the code.

M.

 

[mathewwheeler]

Sure thing example data below

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD]Start Date:[/TD]
[TD]YR1 M1[/TD]
[TD]YR1 M2[/TD]
[TD]YR1 M3[/TD]
[TD]YR1 M4[/TD]
[TD]YR1 M5[/TD]
[TD]YR1 M6[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD].08[/TD]
[TD].17[/TD]
[TD].25[/TD]
[TD].33[/TD]
[TD].42[/TD]
[TD].5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]EMPLOYEE1
[/TD]
[TD]YR1 M1[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]EMPLOYEE2[/TD]
[TD]YR2 M1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]EMPLOYEE3[/TD]
[TD]YR1 M3[/TD]
[TD][/TD]
[TD][/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]

So the return values for the employee start dates would be as below:

EMPLOYEE1: .08
EMPLOYEE2: 1.08
EMPLOYEE3: .25

If I were to change EMPLOYEE1's start date to YR1 M2, I would like for the content in C6 to be cleared based on the VBA criteria (referencing column B). The rule should be, clear the content of any cell where the lookup value is greater than the reference value.