difficulty writing code for changing font color if cell contains specific text

I have a spreadsheet that will contain about 5-15 rows with a letter "S" in the column. If this letter S appears in the column, I need its entire row to change font color to RED and then change that row's value in column L to a negative number. is there any easy way to do this?

I REALLY APPRECIATE IT!

curbster,

Please attach your workbook (or a sample) - scroll down and see "Manage Attachments".

curbster,

See the attached workbook "letterS(1) - SDG09.xls" with macro "FindSinAD".

Open the file and run the "FindSinAD" macro.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

That code works perfectly! Thank you Stanly! There is a little more I have to do and have tried and cannot figure it out. I also have to look for the letter "C" within the same column and mark the rows red and change the column "L" value to a negative number, which I did figure out by duplicating a bit of the code.

The part I am struggling with however is adding to this code that if column "AD" contains an "O" then I need to delete the entire row. The code I wrote does not catch all of the "O"'s and I have to run it twice.

I will include a sample as to show what I'm dealing with. Here is the code I am using:

Sub findSandOinAD()

Dim s As Range 'this WORKS
Dim c As Range 'this WORKS
Dim o As Range 'this does NOT work yet

For Each s In Range("AD2", Range("AD" & Rows.Count).End(xlUp))
  If UCase(s.Value) = "S" Then
    Range(Cells(s.Row, 1), Cells(s.Row, 39)).Font.ColorIndex = 3
    Range("L" & s.Row) = Range("L" & s.Row) * -1
  End If
Next s

For Each c In Range("AD2", Range("AD" & Rows.Count).End(xlUp))
  If UCase(c.Value) = "C" Then
    Range(Cells(c.Row, 1), Cells(c.Row, 39)).Font.ColorIndex = 3
    Range("L" & c.Row) = Range("L" & c.Row) * -1
  End If
Next c


'the code below is to delete rows with an "O" in column AD but does not catch all of them.

For Each o In Range("AD2", Range("AD" & Rows.Count).End(xlUp))
  If UCase(o.Value) = "O" Then
  o.Cells.Select
  Selection.EntireRow.Delete
  End If
Next o
    
End Sub

Thanks for any help!
Kelly

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I strongly recommend that you actually spend a few minutes reading the Forum Rules that you agreed to when you joined. If you don't & continue ignoring them then you will find yourself unable to post!

Thanks for taking the time to read and comply with the rule .

Have you considered using Conditional Formatting?

http://excel-it.com/excel_conditional_formatting.htm

Conditional Formatting will change the colours, but I see you want to change values as well. Unfortunately CF can't do that.

curbster,

Try:

Sub FindS_C_OinAD()
Dim c As Range, LR As Long, a As Long
For Each c In Range("AD2", Range("AD" & Rows.Count).End(xlUp))
  If UCase(c.Value) = "C" Or UCase(c.Value) = "S" Then
    Range(Cells(c.Row, 1), Cells(c.Row, 39)).Font.ColorIndex = 3
    Range("L" & c.Row) = Range("L" & c.Row) * -1
  End If
Next c
LR = Cells(Rows.Count, "AD").End(xlUp).Row
For a = LR To 2 Step -1
  If UCase(Cells(a, "AD").Value) = "O" Then
    Rows(a).EntireRow.Delete
  End If
Next a
End Sub

This should work and will be faster - only looping through the column once

Option Explicit
Option Compare Text    '<-ignores case

Sub findSandOinAD()
    Dim cl     As Range
    Dim rngDelete As Range
    For Each cl In Range("AD2", Range("AD" & Rows.Count).End(xlUp))
        Select Case cl.Value
            Case "S", "C"
                Range(Cells(cl.Row, 1), Cells(cl.Row, 39)).Font.ColorIndex = 3
                Range("L" & cl.Row) = Range("L" & cl.Row) * -1
            Case "O"
                If rngDelete Is Nothing Then
                    Set rngDelete = cl
                Else: Set rngDelete = Union(rngDelete, cl)
                End If
        End Select
    Next cl
    rngDelete.EntireRow.Delete
    Set rngDelete = Nothing
    Set cl = Nothing
End Sub

Roy, Thank you for your help. Yours worked fine unless there were no "O" in column AD, in that case, it would stop the macro and give me a highlighted line. Out of all the files I had to go through only about 5 of them didn't have an "O" in AD so it worked most of the time, thank you!

Stanley, Your code worked for all of my files, thank you, you solved the problem!

Thank you thank you thank you!

Just needs a little error handling in case there is no cells containing O.

Option Explicit

Option Compare Text    '<-ignores case

Sub findSandOinAD()
    Dim cl     As Range
    Dim rngDelete As Range
    For Each cl In Range("AD2", Range("AD" & Rows.Count).End(xlUp))
        Select Case cl.Value
            Case "S", "C"
                Range(Cells(cl.Row, 1), Cells(cl.Row, 39)).Font.ColorIndex = 3
                Range("L" & cl.Row) = Range("L" & cl.Row) * -1
            Case "O"
                If rngDelete Is Nothing Then
                    Set rngDelete = cl
                Else: Set rngDelete = Union(rngDelete, cl)
                End If
        End Select
    Next cl
    On Error Resume Next
    rngDelete.EntireRow.Delete
    On Error GoTo 0
    Set rngDelete = Nothing
    Set cl = Nothing
End Sub