comment if cell contains a certain text

[kchm_2000]

Hi,

I currently have an auto commenter "ME RELATED" based on if the cell has contents that is below the input date or does not have "CCY". However i want to amend the condition such that it will not comment if the selected cell will CONTAIN CCY somewhere in the cell.
Thanks for your help

Public Sub ME1_comment(Date_Ref As Date)

Dim c_ell As Range, S_heet As Worksheet

For Each S_heet In ActiveWorkbook.Sheets
If S_heet.Visible = xlSheetVisible And S_heet.Name <> "Cash Summary" Then
    S_heet.Select
    For Each c_ell In Range("N23", Cells(Rows.Count, 23).End(xlUp))
        If IsDate(c_ell) And c_ell.Offset(0, 25) <> "CCY" Then
            If c_ell <= Date_Ref Then
                c_ell.Offset(0, -12) = "ME RELATED"
                c_ell.Offset(0, -12).Font.ColorIndex = 3
                c_ell.Offset(0, -12).Font.Bold = True
                
            End If
        End If
    Next c_ell
End If
Next S_heet
End Sub

[Leith Ross]

Hello kchm_2000,

You can use the Like operator to check if the cell contains those characters in the cell. I have made the change to the code below.

Public Sub ME1_comment(Date_Ref As Date)

Dim c_ell As Range, S_heet As Worksheet

For Each S_heet In ActiveWorkbook.Sheets
If S_heet.Visible = xlSheetVisible And S_heet.Name <> "Cash Summary" Then
    S_heet.Select
    For Each c_ell In Range("N23", Cells(Rows.Count, 23).End(xlUp))
        If IsDate(c_ell) And (c_ell.Offset(0, 25) Like "*CCY*") Then
            If c_ell <= Date_Ref Then
                c_ell.Offset(0, -12) = "ME RELATED"
                c_ell.Offset(0, -12).Font.ColorIndex = 3
                c_ell.Offset(0, -12).Font.Bold = True
                
            End If
        End If
    Next c_ell
End If
Next S_heet
End Sub

[kchm_2000]

how do you turn off or on the case sensitivity?

[Leith Ross]

Hello kchm_2000,

The easiest way is to convert the string to either lower case, LCase, or to upeer case, UCase and then compare. See the change in bold.

Public Sub ME1_comment(Date_Ref As Date)

Dim c_ell As Range, S_heet As Worksheet

For Each S_heet In ActiveWorkbook.Sheets
If S_heet.Visible = xlSheetVisible And S_heet.Name <> "Cash Summary" Then
    S_heet.Select
    For Each c_ell In Range("N23", Cells(Rows.Count, 23).End(xlUp))
        If IsDate(c_ell) And (UCase(c_ell.Offset(0, 25)) Like "*CCY*") Then
            If c_ell <= Date_Ref Then
                c_ell.Offset(0, -12) = "ME RELATED"
                c_ell.Offset(0, -12).Font.ColorIndex = 3
                c_ell.Offset(0, -12).Font.Bold = True
                
            End If
        End If
    Next c_ell
End If
Next S_heet
End Sub

[kchm_2000]

oh thanks very much, I just have one more query, How do I adjust this formula so that I can present in a case select or if else manner in one sub(). Where for instance have a first criteria in my previous formula below

Dim c_ell As Range, S_heet As Worksheet

For Each S_heet In ActiveWorkbook.Sheets
If S_heet.Visible = xlSheetVisible And S_heet.Name <> "Cash Summary" Then
    S_heet.Select
    For Each c_ell In Range("N23", Cells(Rows.Count, 23).End(xlUp))
        If IsDate(c_ell) And (UCase(c_ell.Offset(0, 25)) Like "*CCY*") Then
            If c_ell <= Date_Ref Then
                c_ell.Offset(0, -12) = "ME RELATED"
                c_ell.Offset(0, -12).Font.ColorIndex = 3
                c_ell.Offset(0, -12).Font.Bold = True
                
            End If
        End If
    Next c_ell
End If
Next S_heet
End Sub

and the second one is this

Dim ce_ll As Range, Sh_eet As Worksheet

For Each Sh_eet In ActiveWorkbook.Sheets
If Sh_eet.Visible = xlSheetVisible And S_heet.Name <> "Cash Summary" Then
    Sh_eet.Select
    For Each ce_ll In Range("M23", Cells(Rows.Count, 23).End(xlUp))
        If IsDate(ce_ll) And (UCase(ce_ll.Offset(0, 25)) Like "*ABC*") Then
            If c_ell <= Date_Ref Then
                ce_ll.Offset(0, -11) = "NOT RELATED"
                ce_ll.Offset(0, -11).Font.ColorIndex = 3
                ce_ll.Offset(0, -11).Font.Bold = True
                
            End If
        End If
    Next ce_ll
End If
Next Sh_eet
End Sub

[Leith Ross]

Hello kchm_2000,

There are two ways to accomplish this. The first answer and easiest would be to make each maco operate only on one section. You could then create a macro using a Case statement to call each macro based on some cell value.

The other method would be to create a Function macro that took arguments and used them in the same loop. This is more complex approach. It can save space and make maintain the code easier. But, it all depends on how similar the routines are.