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

 

[MrKowz]

This worked for me:

Excel 2003<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="BACKGROUND-COLOR: #ffff00">Customer Balance: 321312</TD></TR></TBODY></TABLE><TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>
Custom Number Format:
Formula Is: =LEFT(B1,17)="Customer Balance:"

 

[Mister H]

THANKS MrKowz

I am not sure why it wasn't working for me but I closed Excel and tried again and it is now working.

When it didn't work for me the first time I just assumed I was missing something in the formula.

THANKS Again.

Have a GREAT day,
Mark

 

[Mister H]

Hi MrKowz (OR anyone that can assist )

Can you tell me if there is a way that using conditional formatting (or VBA if needed) that I can search B1 and if it contains WD-CM it turns the cell RED?

The catch is that the WD-CM is not always going to be in the same spot so I can not specify the number of characters to match I just need it to search the entire cell and if that WD-CM appears somewhere in there then change the cell to RED?

Any suggestions?

THANKS,
Mark

 

[VoG]

Try

=ISNUMBER(FIND("WD-CM",B1))

 

[Mister H]

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

 

[VoG]

Hi Mark. Try this

Sub test()
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, "WD-CM") > 0 Then
            .Interior.ColorIndex = 6
            .Offset(, 1).Value = "Do not review"
        End If
    End With
Next i
End Sub

 

[Mister H]

AWESOME. THANSK Peter.

Can you tell me what your formula is saying? I am not really sure of the >0

If InStr(.Value, "WD-CM") > 0 Then

Look in the Text String for the value WD-CM > 0 (so if WD-CM is not found then that would equal 0?)

Just trying to understand. THANKS. This bit of code is going to make sorting through this CRAPPY Report much easier

Take Care,
Mark

 

[VoG]

InStr returns the position of a substring within a string or 0 if the substring isn't found. So if InStr returns a value >0 the substring exists.

 

[Mister H]

THANKS for the explanation Peter

I really APPRECIATE ALL your help now and in the past

Have a GREAT day,
Mark