CODEWITHSUNDEEP
excelvbamacos
TA Arjun
TA Arjun

Reputation: 25

Highlight A cell based on the other cell values

I am trying to highlight a cell under column E if it is Blank and its corresponding value under column C is "Personnel". The below is not working to do the same. Can any one help me with this?

Sub Personnel()
'Check for missing Personnel Information'
    Dim rRng As Range, rRng1 As Range, c
    lRow = Range("A" & Rows.Count).End(xlUp).row
    Set rRng = Range("C1:C" & lRow)
    Set rRng1 = Range("E1:E" & lRow)
      For Each cell In rRng
        If rRng.cell.Value = "Personnel" And rRng1.cell.Value = "" Then
        cell.Interior.ColorIndex = 6
        cell.AddComment "Mapping Info is missing"
        End If

    Next cell
End Sub

Upvotes: 0

Views: 53

Answers (2)

Dominique
Dominique

Reputation: 17471

I see you have created a macro, called "Personel" for doing this, which means that you need to start that macro in order for this highlighting to be done. In quite some cases, this means that people don't know this can be done automatically, using conditional formatting.

Let me explain you how to do this, using conditional formatting: in the conditional formatting page, you choose for "New rule", "Choose a formula" (imagine you want to do this for cell E2), and you enter following formula:

=AND(ISBLANK(E2);C2="Personel")

Why this formula and how do I know it is correct? Very simply: first I enter this formula in another cell (not E2), and I see if the result is "TRUE".

You then apply this conditional formatting for every cell in E-column you need and the highlighting will be performed automatically, without the need of launching a macro.

In case you do want to keep working with VBA for this, please let me know and I'll remove this answer.

Upvotes: 0

urdearboy
urdearboy

Reputation: 14580

  1. You can navigate from one cell to the other with Offset(Row Offset, Column Offset). Since you are looping through Column C and want to check a value in Column D, which is 2 columns to right, you can use rCell.Offset(0, 2)
  2. Qualify your ranges with a worksheet. I declared variable ws to do this. You could also use a With block
  3. Do not use cell, declare a range variable to use for your loop. Here we use rCell to loop through range rRng
  4. Properly indenting your code goes a long way with readability. This will make it easier to debug, update in future, and write as you go along. 
Option Explicit

Sub Personnel()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim rRng As Range, rCell As Range, LR As Long

LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
Set rRng = ws.Range("C1:C" & LR)

    For Each rCell In rRng
        If rCell = "Personnel" And rCell.Offset(0, 2) = "" Then
            rCell.Interior.ColorIndex = 6
            rCell.AddComment "Mapping Info is missing"
        End If
    Next rCell

End Sub

Upvotes: 2

Related Questions