VBA enter value adjacent cell changes color

[Pinaceous]

Hi All,

I'm working with a column I11:I180; where if a value is entered into this column, I'd like the adjacent cell to turn red until a value is entered into that cell.

For example, if I enter a value into cell I11, the cell H11 will turn red until a value is entered here.

Likewise, if I enter in a value into cell I12, the cell H12 will turn red until a value is entered here and so on until cell I180 of that column.

I'd prefer to use a VBA code in lieu of data validation option.

Can you help me figure this out?

Thank you!
pinaceous

 

[Pinaceous]

Thanks EXCEL MAX!

Let me give it a go & again, and thanks again for your code!!

-pinaceous

 

[BSALV]

i don't know any event of this kind "Private Sub ColumnI_ChangeH(ByVal Target As Range)"
How does that work ??? Is it another macro that triggers this one ?

This macro works always from the point of view of Column H, even if you change something in I, so you don't need a 2nd macro that works like a mirrow.
The last modifciation you asks, is simply deleting H when I is empty. In the previous version, that was only done when H was "Enter KG", so the bold text is slightly different and i added those application.enableEvents lines.

Private Sub Worksheet_Change(ByVal Target As Range)
     Set c = Intersect(Target, Range("H11:I180"))               'just check the changes in range H11:I80
     If Not c Is Nothing Then                                   'nothing changed in that range
          Set c = Intersect(c.EntireRow, Range("H11:H180"))     'for those changed cells look always in the H-column even if you changed the I-column
          For Each cl In c.Cells
               b = ((cl.Value = "" Or cl.Value = "Enter KG") And cl.Offset(, 1).Value <> "")     'H is red when it's empty or "Enter KG" and I isn't empty
               cl.Interior.Color = IIf(b, RGB(255, 0, 0), xlNone)     'give right color to H
               Application.EnableEvents = False
               If b And cl.Value = "" Then cl.Value = "Enter KG"     'only add "Enter KG" when H cell is empty (to prevent endless loop)
               If cl.Value <> "" And cl.Offset(, 1).Value = "" Then cl.Value = ""     'also when I is empty again and H isn't, then delete H
               Application.EnableEvents = True
          Next
     End If
End Sub

 

[Pinaceous]

BSALV!!​

Hey how is it going?

In testing out your code, I really appreciate it very much!

If I enter in a value in Column H, ie at H37, then delete it, it will clear out both cell contents of H37 and I37, wonderful job!

However, in working with your code, could you possibly modify it so that when you enter in a value in Column I, ie I37 that it will clear out both contents of cells I37 and H37 in that same manner as above?

Thank you!
pinaceous

 

[BSALV]

??? Bizar.
You write something in I37 and the macro clears the content of H37:I37 ?
I don't understand the logic.

insert this just in front of that application...=true

 If cl.offset(,1).Value <> ""  Then cl.resize(,2).clearcontents 'something in the I = clear H&I
Application.EnableEvents = True