Implement this IF Statement into Cond. Formatting. HOW?

[Mister H]

Hi All:

I am wondering how I can put this If statement into Conditional Formatting:

IF(LEFT(B1,17)="Customer Balance:","DELETE",""))

I want to put in a condition that will simply say IF the above condition is met (first 17 letters = Customer Balance: then change the cell to Yellow

Hope this makes sense. I tried this in the Conditional Formatting but it did not work for me? I did not get an error (like the other fromulas I tried) but it did not turn my cell Yellow even though it started with Customer Balance:

=LEFT(B1,17)="Customer Balance:"

Any suggestions?

THANKS,
Mark

 

[Mister H]

Hi Peter:

I am wondering if you can assist me a little further. I am using your code to find another item in my list:

Sub Mark_CMUs()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("B" & i)
        If InStr(.Value, "CMU") > 0 Then
            .Interior.ColorIndex = 6
            .Offset(, 1).Value = "Do not review"
        End If
    End With
Next i
End Sub

This is almost working but I am wondering if this line of code can be revised to add another identifier?

If InStr(.Value, "CMU") > 0 Then

I only want items to identify the cells that have CMU and a number (no spaces).

Example:
CMU765-1234 would be marked Do Not Review

CMU ABs Would NOT be marked (skipped)

CMU123 would be marked Do Not Review

Hopefully this makes sense.

THANKS,
Mark

 

[VoG]

Hi Mark. Can I suggest that you start a new thread. I think that you will need a pattern matching algorithm probably involving Regular Expressions. That isn't my forte.

 

[Mister H]

THANKS Peter... I just saw your post. I will do as you suggested. THANKS for taking the time to reply.

Take Care,
Mark

 

[Peter_SSs]

THANKS Peter That Works

Would I be able to use that same formula in VBA? If I needed to search from row 20000 UP is there an easy way to do that? I have not yet searched Mr Excel but I can if you can't provide a simple solution. I am asking because I have already used two of the conditions in Conditional Formatting and may need the 3rd for something else.

So, can VBA

check column B and wherever the WD-CM is found make the B Cell RED and maybe put "Do Not Review" in Column C (same row of course).

THANKS Again Peter,
Mark

Consider this non-looping alternative. It assumes a heading in row 1. Test in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> RedCells()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("B1", Range("B" & Rows.Count).End(xlUp))<br>        .AutoFilter Field:=1, Criteria1:="*WD-CM*"<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)<br>            .Interior.ColorIndex = 3<br>            .Offset(, 1).Value = "Do not review"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        .AutoFilter<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>