How do you delete values in a cell based on the cell color (black)

[mathewwheeler]

Right now, I have the rows conditionally formatted (to become black) based on the value in column B. If there are values in row 7, but B7 changes cells D7:K7 to be formatted black, how can I automatically delete the values in cells D7:K7 based on the conditional format?

 

[alansidman]

Using VBA, you would apply the same conditions (criteria) to clear the cells D7:K7 that you used in your Conditional Format scenario.

 

[mathewwheeler]

Is that complex? 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.

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.

 

[alansidman]

Following your explanation and trying to visualize it is confusing. Suggest you post several examples and explain what is happening in the examples. What is clear in your head is really cloudy in mine. Make sure you ID the columns and rows that the data is located in when you post.

 

[mathewwheeler]

Sure thing. To start off, the start date format is Year Month, so YR1 M6 indicates that the employee was hired 6 months into year 2. Example below:

[TABLE="class: cms_table_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 lookup values for the employee start dates would be as below:

EMPLOYEE1: .08 (no black cells)
EMPLOYEE2: 1.08 (C7:H7 black)
EMPLOYEE3: .25 (C8:D8 black)

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 (reference to column B). The rule should be, clear the content of any cell where the lookup value is greater than the reference value.

If I were to change EMPLOYEE3's start date to YR1 M6, I would like to see the content in E8:G8 cleared.

 

[alansidman]

Because your YR1 M1 methodology is text based and not really date based, I see no way of doing this. If you change these to actual dates which in excel are numerical then the dates can be tested for greater than or less than or equal to and a solution may be in the offing. Currently I see no solution. Hopefully, someone else may jump in with an out of the box means of comparing these "dates."

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cl As Range
   If Not Intersect(Target, Range("B:B")) Is Nothing Then
      For Each Cl In Intersect(Target.EntireRow, Range("C:BJ"))
         If Cl.DisplayFormat.Interior.Color = 0 Then Cl.ClearContents
      Next Cl
   End If
End Sub

This needs to go in the relevant sheet module & will automatically run when you change a value in col B

 

[mathewwheeler]

Worked BEAUTIFULLY. Thank you very much for your willingness to help.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Marcelo Branco]

Duplicate
https://www.mrexcel.com/forum/excel...sed-conditional-formatting-cell-same-row.html

M.