Offset interior color of a blank cell if adjacent cells have a value.

[Livin404]

I have part of a macro and I think I have a couple of concepts correct; however, I'm trying to pieces them with components I'm missing. If starting on K5 and going down, for all cells that have any value. In this case if the adjacent cells in Column L are blank then the cell will be filled in with yellow. For example Cell K8 has "1105" and L8 is blank it will be colored. If it is not blank then nothing happens.

I know I'll need an "else" statement, and I don't know where I'll put my If blank statement( "">

I think I have a couple things I can use in the macro below.

Thank you so much for your patience.

Sub Offset_interior()
    Dim Kl As Range
   For Each Kl In ActiveSheet.Range("L5:L" & ActiveSheet.Cells(Rows.Count, 11).End(xlUp).Row)
      If K1.Value Like "*" Then K1.Offset(, 1).Interior.Color = RGB(255, 255, 0)
   Next K1
End Sub

 

[hajiali]

instead of VBA could you not just use conditional formatting?

Book1
	K	L
8	1234	678
9	644
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L8:L100	Expression	=AND($K8<>"",$L8="")	text	NO

 

[Livin404]

I wish I could but this worksheet will be used everyday by numerous people. Part of this I have a macro which clears everything, so I can paste the new template. There are about 10 other macros in this worksheet. Multiple columns are cut, inserted and moved when my Run all option is triggered.

There is a generated PDF from another system which I then save as an excel which is then pasted on my worksheet containing my multiple macros and then my program kicks in.

Thank you

 

[Livin404]

instead of VBA could you not just use conditional formatting?

Book1
	K	L
8	1234	678
9	644
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L8:L100	Expression	=AND($K8<>"",$L8="")	text	NO

I will try the conditional formatting, I will look into finding a VBA that I can still clear page without wiping out my conditional formatting.

Thank you,

 

[hajiali]

try this on the sheet code
right click on the sheet name
choose View code
and past the below.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rgK, rgL As Range
Set rgK = Range("K:K")
Set rgL = Range("L:L")

If Not Intersect(Target, rgK) Is Nothing Then
    If Target <> "" And Target.Offset(, 1) = "" Then
        Target.Offset(, 1).Interior.Color = RGB(255, 255, 0)
        Exit Sub
    End If
    If Target = "" Then
        Target.Offset(, 1).Interior.Color = xlNone
        Exit Sub
    End If
End If
If Not Intersect(Target, rgL) Is Nothing Then
    If (Target <> "" And Target.Offset(, -1) <> "") Or (Target = "" And Target.Offset(, -1) = "") Then
        Target.Interior.Color = xlNone
        Exit Sub
    End If
    If Target = "" And Target.Offset(, -1) <> "" Then
        Target.Interior.Color = RGB(255, 255, 0)
        Exit Sub
    End If
End If
End Sub

let me know if you encounter any issues

 

[DanteAmor]

For example Cell K8 has "1105" and L8 is blank it will be colored

If you want to run the macro, try the following option:

Sub ColorColumnL()
  Dim lr1 As Long, lr2 As Long
  lr1 = Range("K" & Rows.Count).End(3).Row
  With ActiveSheet.Range("K4:L" & lr1)
    .Interior.ColorIndex = xlNone
    .AutoFilter Field:=1, Criteria1:="=*"
    .AutoFilter Field:=2, Criteria1:="="
    lr2 = Range("K" & Rows.Count).End(3).Row
    If lr2 > 4 Then Range("L5:L" & lr2).Interior.Color = vbYellow
    .AutoFilter
  End With
End Sub

If you want it automatic, put the following code in the events of your sheet. Every time you write something in column K or L it will highlight yellow if necessary.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range
  
  Set rng = Intersect(Target, Range("K5:L" & Rows.Count))
  If Not rng Is Nothing Then
    For Each c In rng
      With Range("L" & c.Row)
        If Range("K" & c.Row).Value <> "" And .Value = "" Then
          .Interior.Color = vbYellow
        Else
          .Interior.ColorIndex = xlNone
        End If
      End With
    Next
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
----- --