PDA

View Full Version : [SOLVED] Formula to change text colour and add a value to another cell



sandam
03-15-2005, 03:52 AM
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
03-15-2005, 04:20 AM
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
03-15-2005, 04:28 AM
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
03-15-2005, 04:55 AM
Go to Format/Conditional Formatting and inset the condition as below.
Note that the case of the letters doesn't matter here.

johnske
03-15-2005, 04:55 AM
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
03-15-2005, 06:42 AM
thanks to both of you. :) your help is much appreciated.

folk754
03-15-2005, 12:49 PM
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
03-15-2005, 01:36 PM
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
03-16-2005, 02:25 AM
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;?