Highlighting color in cell value based on criteria

[jawaharprm]

Hi all,
i worked on code to highlight color in cell value for 2 conditions
1. duplicate to highlight in green
2. if any space before and after to highlight in blue

the code is working fine but the issues if i call the two macro one time only one code is working
or if i run only duplicate the 2 code if the condition meets it turn's to white color,

pls help to how to run both codes using call, i enclosed sample file for your reference.

Sub test()
Call duplicationhighlight
Call HighlightSpaces
End Sub

Sub duplicationhighlight()
Dim ws As Worksheet
Dim lr As Long
Dim rng As Range
Dim cell As Range
Dim col As Variant

Set ws = ThisWorkbook.Sheets("Input_File")

For Each col In Array("D", "E", "F")
lr = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
Set rng = ws.Range(col & "4:" & col & lr)

rng.Interior.ColorIndex = xlNone

For Each cell In rng
If cell.value <> "" Then
If Application.WorksheetFunction.CountIf(rng, cell.value) > 1 Then
cell.Interior.Color = vbGreen

End If
End If

Next cell
Next col

End Sub

Sub HighlightSpaces()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim checkCols As Variant
Dim col As Variant
    
Set ws = ThisWorkbook.Worksheets("Input_File")
checkCols = Array("D", "E", "F")
    
For Each col In checkCols
Set rng = ws.Range(col & "4:" & col & ws.Cells(ws.Rows.Count, col).End(xlUp).Row)
        
For Each cell In rng
If Len(cell.Text) > 0 Then

' Check first or last character of displayed text
If Left(cell.Text, 1) = " " Or Right(cell.Text, 1) = " " Then
cell.Interior.Color = vbBlue
cell.Font.Color = vbWhite
Else

cell.Interior.Color = xlNone
cell.Font.Color = vbBlack
End If
Else

' Empty cell ? clear formatting
cell.Interior.Color = xlNone
cell.Font.Color = vbBlack
End If
Next cell
Next col
End Sub

thanks
jawahar prem

 

[MikeVol]

The problem is that both macros clear the fill:

rng.Interior.ColorIndex = xlNone

and the second macro does the same:

cell.Interior.Color = xlNone

So the first macro colors the cell green, and the second one runs—and resets the color. Ultimately, only the result of the last code run remains. Therefore, we'll combine the checks into ONE macro; in my opinion, that's more appropriate.

Option Explicit

Sub HighlightDuplicatesAndSpaces()
    Dim lr          As Long
    Dim col         As Variant
    Dim rng As Range, cell As Range

    With ThisWorkbook.Worksheets("Input_File")

        For Each col In Array("D", "E", "F")
            lr = .Cells(.Rows.Count, col).End(xlUp).Row
            Set rng = .Range(col & "4:" & col & lr)

            For Each cell In rng
                cell.Interior.ColorIndex = xlNone
                cell.Font.Color = vbBlack

                If cell.Value <> "" Then

                    If Left(cell.Text, 1) = " " Or Right(cell.Text, 1) = " " Then
                        cell.Interior.Color = vbBlue
                        cell.Font.Color = vbWhite

                    ElseIf Application.WorksheetFunction.CountIf(rng, cell.Value) > 1 Then
                        cell.Interior.Color = vbGreen
                    End If

                End If

            Next cell

        Next col

    End With

End Sub