Add * at cell

Option Explicit

Sub crsCheck()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim lastRow As Long
Dim r As Range
Dim myCell As Range
Dim myColor As Long
Dim bColorFound As Boolean

    myColor = 14536083 'some type of blue/greenish color
    
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Data")
    
    lastRow = wks.Range("A" & Rows.Count).End(xlUp).Row
    
    If lastRow < 6 Then Exit Sub 'no data to process
    
    Set rng = wks.Range("A6", wks.Cells(wks.Rows.Count, "D").End(xlUp))
    
    For Each r In rng.Rows
        For Each myCell In Range(r.Address)
            If myCell.Interior.Color = myColor Then
                bColorFound = True
                Exit For
            End If
        Next myCell
        
        If bColorFound Then
            wks.Range("D" & r.Row).Value = wks.Range("D" & r.Row).Value & "*"
        End If
        bColorFound = False
    Next r
End Sub

Select allOpen in new window

Here's an addition - one to remove the cross checking asterisk as well.

Option Explicit

Sub crsCheck()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim lastRow As Long
Dim r As Range
Dim myCell As Range
Dim myColor As Long
Dim bColorFound As Boolean

    myColor = 14536083 'some type of blue/greenish color
    
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Data")
    
    lastRow = wks.Range("A" & Rows.Count).End(xlUp).Row
    
    If lastRow < 6 Then Exit Sub 'no data to process
    
    Set rng = wks.Range("A6", wks.Cells(wks.Rows.Count, "D").End(xlUp))
    
    For Each r In rng.Rows
        For Each myCell In Range(r.Address)
            If myCell.Interior.Color = myColor Then
                bColorFound = True
                Exit For
            End If
        Next myCell
        
        If bColorFound Then
            wks.Range("D" & r.Row).Value = wks.Range("D" & r.Row).Value & "*"
        End If
        bColorFound = False
    Next r
End Sub
Sub undoCrsCheck()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim lastRow As Long
Dim r As Range
Dim myCell As Range
Dim myColor As Long
Dim bColorFound As Boolean
Dim fRange As Range
Dim firstAddress As String

    myColor = 14536083 'some type of blue/greenish color
    
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Data")
    
    lastRow = wks.Range("A" & Rows.Count).End(xlUp).Row
    
    If lastRow < 6 Then Exit Sub 'no data to process
    
    Set rng = wks.Range("A6", wks.Cells(wks.Rows.Count, "A").End(xlUp))
    Set rng = rng.Offset(0, Columns("D").Column - 1)
    
    Set fRange = rng.Find(what:="Log", LookIn:=xlValues, lookat:=xlPart)
        
    Set fRange = rng.Find(what:="~*", LookIn:=xlValues, lookat:=xlPart)
    If Not fRange Is Nothing Then
        firstAddress = fRange.Address
        Do
            fRange.Value = Replace(fRange.Value, "*", "")
            Set fRange = rng.FindNext(fRange) '(what:="~*", after:=fRange, LookIn:=xlValues, lookat:=xlPart)

        Loop While Not fRange Is Nothing 'And fRange.Address <> firstAddress - not needed as making change to search term
    End If
End Sub

Select allOpen in new window

Attachment

Dave
Cross-Check.xls

Hi Dave,

I noticed the "*" only appeared if I manually highlight the cell, e.g. in the attached sample workbook, Cell C32 was filled the color manually, the rest of the color was updated automatically through other sub module ( i using separate module to check data validation, if the data not matched, the cell will automatically highlighted in blue). Is a chance to add "*" if the color was automatically filled in by other macro?
Cross-Check.xls

You just need to test for the SAME color number.  The color number in the file you uploaded was 16764057, while the color number in the original post was 14536083

Since your macro will be doing the marking you can change my line: 13 (and delete line 50 as that's never used) to myColor = 16764057.

The macro that's doing the coloring - does it use this number or does it use a constant word?

What is the command in the macro doing the coloring?

Dave

When you do it manually, you may not be picking the exact same color - or something lost in the translation of color pallets between your version and my version of Excel (or our implementations).  But if you use the same color NUMBERS from your coloring macro with my crsCheck macro, you can't lose ;)

Dave

Hi Dave,

You're right. This number (16764057) giving me the '*".
Is that a way to add-in "*" only once, if I re-run again the sub the existing data with "*" should not updated with another "*". Currently I have multiple * if I hit the sub again.

Hi Dave,

I managed to do this with your additional solution "undo *". Thanks.

Thanks a lot for the help Dave.