Look for yes in one column n remove cell color from first column in that row

[trevor2524]

Hello Everybody, I have a question question hopefully somebody can help me with the programming. I want to create a macro to search Column I to see if it says the word "Yes" in a certain row if it does it removes the cell color from Column A in that row. It will then move onto the next row. If Column I says "No" it will skip that row and move onto the next row. I would need it to do a continuous loop until it finds a blank row. I appreciate the help on any coding you may have. Thank You.

[Solus Rankin]

Try:

Please Login or Register  to view this content.

Untested.

[LaffyAffy13]

Please Login or Register  to view this content.

Haven't tested this yet because I have a long macro running on my computer right now, but try it out in a test workbook to see if it works.

[LaffyAffy13]

Lol didn't see Solus's post

[trevor2524]

Hey Thanks for the reply, The macro works but unfortunately it doesn't remove the color from the cell. That's my fault I should have included the following code to show you how I'm formatting the cell with the color. Here it is hopefully this is helpful in helping me.

Sub FindDups()
'
' NOTE: You must select the first cell in the column and
' make sure that the column is sorted before running this macro
'
Application.ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell <> ""
If FirstItem = SecondItem Then
ActiveCell.Offset(0, 0).Interior.Color = RGB(255, 0, 0)
ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0)
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
End If

Loop

End Sub

[Solus Rankin]

Trevor,

I don't understand, the goal of the macro (its only action) was to remove the formatting, how did it work if it didn't remove the color?

Also, why use off set if you're not offsetting any rows or columns?

ActiveCell.Offset(0, 0).Interior.Color = RGB(255, 0, 0)

[trevor2524]

This code is basically another step of a much larger macro. I was just showing you that so you knew how I did the formatting for the cell color earlier. Which carries over into this portion of the coding. When I said it was working I mean that it runs through goes thru each line and looks for the correct stuff. Unfortunately, it is not removing the cell color and I didn't know if that was because I formatted it differently earlier. I don't really wanna change the coding of the FindDups Macro because this programming has been working for a while. My apologies for not making much sense on this and hopefully this cleared some questions up.

[Solus Rankin]

If I use this:

Please Login or Register  to view this content.

to set cell A1 to red, and type yes into I1 and run this:

Please Login or Register  to view this content.

It removes the red color from A1.

[LaffyAffy13]

My code also does the job.

You may have preset conditional formatting for your cells. That might reverse the code depending on the formatting. If so, try adding this into the code.

Please Login or Register  to view this content.

[trevor2524]

Thank you it worked!