VBA If statements help 1

[wom2007]

Hi, I am trying to write a VBA code in excel that gives me a response in the correspnding row based upon whether or not the condition i specified is met.

Example:

I want to tell Excel to go to Column A, search all the cells, and If the cell has a font that is red, go to column B and put the text "To Be Deleted"

I know a bit about VBA but I am still fairly new, any help woulb be appreciated. Thanks.

 

[radubalmus]

you can do a loop...

Dim count As Integer

count = 1
Do Until Sheet1.Cells(count, 1) = "" 'shearch a column until finds an empty cell(i don't know your condition. this is a case..)
count = count + 1
If Sheet1.Cells(count, 1).Font.ColorIndex = 3 Then ' if font of the cell is red
Sheet1.Cells(count, 1).Offset(0, 1).Text = "To Be Deleted" 'move next cell to the right and write "To Be Deleted"
End If
Loop


There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[wom2007]

Hi, I tried it but for some reason it did not work. Can you tell me what to specify if i want the program to look in the first column (column A) and find all the cells with fonts that are red and then in column B, input the text "To Be Deleted".

As an example, I have Kmart in the very first cell, it is highlighted in red. I would like the program to go directly to the right and type "To Be Deleted". I know this is probably fairly simple but I am still strugling with the basics. Thanks.

 

[GlennUK]

When you say that cells are highlighted in red, you don't say whether they have been formatted that way normally, or by Conditional Formatting. Which is it?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

wom2007,

You have to know something about basic Excel features before you begin some fancy programming.

As Glenn implied, there's Formatting and there's Conditional Formatting: BIG DIFFERENCE in this kind of situation.

radubalmus's solution will work for Formatting but will not work for COnditional Formatting, 'cuz the Font FORMAT Color, in the latter instance, is NOT RED.

Skip,

_[red][/red]

 

[wom2007]

Wow, it did not know their was a difference. Can I get the code for either of the two please. Thanks.

 

[Andrzejek]

I have Kmart in the very first cell, it is [blue]highlighted in red[/blue]

Another question is:
1. Is the Font.ColorIndex = 3 (red) or
2. Is it .Interior.ColorIndex = 3 (red)

That would make a difference.....

Have fun.

---- Andy

 

[xlbo]

Can I get the code for either of the two please

This is not a helpdesk. As such, we do not generally offer complete coded solutions. You will get the most help if you research how to do something, try it and then post back if/when you get an error.

As a starter for 10

You need to LOOP through the rows of data
You need to check for either the INTERIOR.COLORINDEX or the FONT.COLORINDEX depending on whether the text is red or the cell background is red
When you find a cell matching your criteria, you have a choice of how to write to that cell but some key words are OFFSET or RANGE

IF statements are written in the form of

IF (statement) THEN
'execute code for true part
ELSE
'execute code for false part
END IF

LOOPS come in a variety of forms but 2 common ones used for purposes such as yours are FOR EACH...NEXT and FOR i = x to y NEXT i type constructs.

the F1 key is your friend

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[WalkerEvans]

My "newbie" days are not that far behind me, so I empathize. What you have said so far leads me to believe that it is the font color that is red; if that is the case the following should work for you:

Application.GoTo Reference:=Range("A1")
Do
Checker = ActiveCell.Value
If Checker = False Then Exit Do
If ActiveCell.Font.ColorIndex = 3 Then
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = "To Be Deleted"
ActiveCell.Offset(0, -1).Activate
End If
ActiveCell.Offset(1, 0).Activate
Loop

Try this; it should give you what you need. If it does, spend some time analyzing it using the VBA "Help" function in order to figure out what was done, how, and why. It will give you a boost on your own learning path.

 

[xlbo]

WalkerEvans - a couple of tips for you.

Don't bother with activate - it is unnecessary and slows down code

These 2 lines:
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = "To Be Deleted"

can be written as

ActiveCell.Offset(0, 1).Value = "To Be Deleted"

As a basic rule, if you have a SELECT or ACTIVATE at the end of a line and the next line starts with either SELECTION or ACTIVE..., you can get rid of the selection or activation and perform the action directly to the cell


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[WalkerEvans]

Geoff,

Thanks for the tips! Anything I can do to cut down on the amount of code is always welcome. Sometimes I get locked into a way of doing things and don't notice that there are "extras" in the code that I can do without.

wom2007: Modify my suggested code per xlbo's tips.

 

[xlbo]

no probs - to be honest, I'm not a fan of using Activecell or DO WHILE constructs but that is mainly due to what and how I learned. Personally, I would use something like

Dim c as range
For each c in Range("A1:A" & Range("A1").end(xldown).row)
if c.interior.colorindex = 3 then 
   c.offset(0,1).value = "To Be Deleted"
end if
next

But then again, rather than using colours, I would've set up a piece of data to indicate which rows to delete and then just filtered on that data and deleted the whole lot in one fell swoop !! cat skin ways many !

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

No WUNDER that cat has 9 lives! ;-)

Skip,

_[red][/red]