Formula to change text colour and add a value to another cell

[sandam]

Hi there. this is my first post in the excel help section so bear with me .

One of our marketing people asked me if its possible to look at the text value of a cell (either yes/no/maybe) and if its yes, change the text colour in the cell to Red and add +1 to anothers cells count value. I'm not entirely sure how to do this and also would like to know if its possible to do it with just formula's and not with VBA, if not what event woudl I have to check for to do this?. This formula would need to be applied to a column.

thanks in advance
Andrew;?

[mdmackillop]

The first part you can do with conditional formatting. The second part would require some vba

Option Explicit
Option Compare Text 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column() <> 1 Then Exit Sub
    If Target = "yes" Then
        Target.Offset(0, 2).Value = Target.Offset(0, 2) + 1
    End If
End Sub

[sandam]

Thanks again MD.

I'm not allowed to download files at work, so if possible could you post an example of how to do the conidtional formatting or point me in the direction of a KB entry?

[mdmackillop]

Go to Format/Conditional Formatting and inset the condition as below.
Note that the case of the letters doesn't matter here.

[johnske]

Hi Sandam,

Click Format/Conditional Formatting, on the left, select Condition1/Cell Value is, in the middle box, select "is equal to" and then type yes in the right-hand box. Now select Format/Patterns (click red) then click OK, and then on the conditional formatting dialog box click OK.

You can do this with a single cell or a selection. You can also extend it by including a formatted cell in your selection then going back to Conditional Farmatting....

Regards,
John

[sandam]

thanks to both of you. your help is much appreciated.

[folk754]

Not sure if the part of "counting cells" was rplied to. So you can basically do a formula '=countif("Yes", B1:B200")'. I believe that's the way it goes.

Good luck.

[Zack Barresse]

Not sure if the part of "counting cells" was rplied to. So you can basically do a formula '=countif("Yes", B1:B200")'. I believe that's the way it goes.

Good luck.

Actually, you have it backwards folk. The Range is the first syntax, the Criterion is the second syntax. In your example, it should actually be ..

=COUNTIF(B1:B200,"Yes")

Take care.

[sandam]

Thanks again to everyone for their suggestions and explanantions. the conditionl formatting and the countif formula did the job and marketing are happy bunnies.

Andrew;?