Validate Range of Cells dynamically using VBA

[Sai Prabhu]

Hi,

I’m new to VBA and need help on the below questions. Sample file included.

Question - 1:

I’m working on a Leave Tracker and the requirement is that the User should not enter CL in more than two consecutive cells in rows.

CL CL CL - Not Allowed (pass with current code)

CL CL Blank CL CL - Allowed

CL CL CL CL CL CL - Not Allowed (failed with current code)

The code is working when the User enter data in each cell, however, it fails when the User copies data to range of Cells (More than 3 Cells).

Question - 2:

When the Excel file is opened from Outlook, how to disable Protected View message and Enable Editing programmatically? I tried few solutions available online, but that is not working.

Thanks.

 

[vletm]

@Sai Prabhu
One sample for Question 1...

 

[Sai Prabhu]

@Sai Prabhu
One sample for Question 1...

Thanks for your response.

 

[shrivallabha]

You can also employ conditional formatting also.

1. Select all applicable ranges (first two columns can be excluded).
2. Then in conditional formatting box put in formula : =AND(C1="CL",COUNTIF(OFFSET(C1,0,-2,1,2),"CL")=2)
3. Apply conditional formatting as you want.

It will highlight incorrectly filled cell.