Hi Everyone
can someone help please
let say I have in Sheet 1 in cells CW 24 =yes,CW 25 =yes,CW 26 =yes,CW 27 =yes,CW 28 =yes
If all thes cells are yes I want a macro to give me a message box "Successfully completed!"

Thnxs for your valuable help
Bakar

Hi,

Try this code.

Sub Check()
Dim Rng As Range
Set Rng = Range("CW24:CW28")
If Application.WorksheetFunction.CountIf(Rng, "<Yes") = 0 Then
MsgBox "All cells contain YES", vbInformation
Else
MsgBox "One of the cells doesn't contain YES", vbInformation
End If
End Sub

Just a suggestion. You should try to give the range a meaningful name such as SearchRange or something. Also, are you going to run this macro on your own or should it run with an event such as changing the CW24:CW28 range?

I hope this helps you.

Prashant

On Wednesday, October 17, 2012 8:59:04 AM UTC-7, Bakar wrote:
Hi Everyone

can someone help please

let say I have in Sheet 1 in cells CW 24 =yes,CW 25 =yes,CW 26 =yes,CW

27 =yes,CW 28 =yes

If all thes cells are yes I want a macro to give me a message box

"Successfully completed!"



Thnxs for your valuable help

Bakar









--

Bakar

Hi Bakar,
Try this, meanwhile I will try to do it with a macro as you asked.

=IF(COUNTIF(CW24:CW28,"yes")=5,"S-C","")

Regards,
Howard

On Wednesday, October 17, 2012 8:59:04 AM UTC-7, Bakar wrote:
Hi Everyone

can someone help please

let say I have in Sheet 1 in cells CW 24 =yes,CW 25 =yes,CW 26 =yes,CW

27 =yes,CW 28 =yes

If all thes cells are yes I want a macro to give me a message box

"Successfully completed!"



Thnxs for your valuable help

Bakar









--

Bakar

Here is a message box code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.WorksheetFunction.CountIf(Range("CW24: CW28"), "yes") = 5 Then
MsgBox "Successfully completed!", vbOKOnly, "Get'er Done"
End If
End Sub