VBA Clear Contents

[Pinaceous]

Hi All,

I'm working with a Column H, where sometimes it provides an Interior.Color = IIf(b, RGB(255, 0, 0) with a specific value of "Enter Texts".

Is there a code that I can run that when this happens for example at cell H11 and when cell I11 is blank that I can run to allow me to clear the contents of H11?

Also, when this happens at cell H12 and when cell I12 is blank that I can run to clear the contents of H12?

And so on...

Please let me know!

Thank you!
Pinaceous

 

[bebo021999]

Assum column H starts from H11 and down
It loops column I to find blank cell, then delete column H content

Sub test()
Dim cell As Range
For Each cell In Range("I11:I" & Cells(Rows.Count, "H").End(xlUp).Row)
    If IsEmpty(cell) Then cell.Offset(0, -1).ClearContents
Next
End Sub

 

[Pinaceous]

Assum column H starts from H11 and down
It loops column I to find blank cell, then delete column H content

Sub test()
Dim cell As Range
For Each cell In Range("I11:I" & Cells(Rows.Count, "H").End(xlUp).Row)
    If IsEmpty(cell) Then cell.Offset(0, -1).ClearContents
Next
End Sub

Thanks bebo021999!

My column count is long and the code does take time to run.

Do you know if more variables can be added to speed this process up?

Many thanks!
pinaceous

 

[bebo021999]

Try again:

Sub clear()
Range("I11:I" & Cells(Rows.Count, "H").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Offset(0, -1).ClearContents
End Sub

 

[Fluff]

Another option

Sub Pinaceous()
   With Range("H2", Range("H" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if((@=""Enter Texts"")*(" & .Offset(, 1).Address & "=""""),"""",@)", "@", .Address))
   End With
End Sub

 

[Pinaceous]

Hey Fluff,

Your code works well.

But, can you please adjust your code to ignore any entry that is not an interior color Red with "Enter Texts" for example a number entry and to please ignore any blank cells?

Because right now, it is providing a "0" for any blank cells, which I do not want it to do.

Thank you!
Respectfully,
pinaceous

 

[Fluff]

It already ignores any cell that does not have "Enter Texts", so not sure what you mean.