VBA Help - Highlight Cells that Contain Lowercase Letters

[aeday]

Hello,

I am searching for a few lines of code in Excel that would search a column (D4:D999) and highlight any cell that contained lowercase letters yellow.

In addition I would also like the code to search a separate column (G4:G99) for a name. If the cell equals the name, then look at column E4:E999 and highlight yellow any cells that are missing a dash ( - ).

I have tried to Google separate lines of code and piece them together, but I have not been able to find the right combination. Please let me know if further details are needed.

Thank you for your help,
Andrea

 

[Yongle]

I am searching for a few lines of code in Excel that would search a column (D4:D999) and highlight any cell that contained lowercase letters yellow.

Code is not required

Can use conditional formatting
- select range D4:D999
- Home Tab \ Conditional Formatting
- new CF rule
- use formula to determine which cells to format
- enter this formula
=EXACT(D4,UPPER(D4))=FALSE
- click on FORMAT \ FILL \ click on YELLOW

 

[Rick Rothstein]

If you need to use VBA code, then here is a macro that you can use (change the red text to the Name you actually want to search for)...

Sub aeday()
  Dim R As Long, SomeName As String, Arr As Variant
  Range("D4:D999").Interior.Color = xlNone
  Range("E4:E99").Interior.Color = xlNone
  Arr = Range("D4:D999")
  For R = 1 To UBound(Arr)
    If Arr(R, 1) Like "*[a-z]*" Then Cells(R + 3, "D").Interior.Color = vbYellow
  Next
  SomeName = "[B][COLOR="#FF0000"]NameToFind[/COLOR][/B]"
  Arr = Range("E4:G99")
  For R = 1 To UBound(Arr)
    If Arr(R, 3) = SomeName Then
      If Not Arr(R, 1) Like "*[-]*" Then Cells(R + 3, "E").Interior.Color = vbYellow
    End If
  Next
End Sub

 

[Yongle]

I would also like the code to search a separate column (G4:G99) for a name. If the cell equals the name, then look at column E4:E999 and highlight yellow any cells that are missing a dash ( - )

Can also be done with conditional formatting as below, where the name being searched for is in cell H1

Select E4:E999
CF \ New Rule \ Use formula ...
=AND(G4=$H$1,COUNTIF(E4,"<>*-*")>0)