Excel Macros: Checking Data and coloring using excel VBA

Question

I have a Column "C" in sheet2 which can have 3 standard values say (Yes, No, NA) say till 100th row. Now in Sheet1 column G5 & G6, I want to output the result based on following conditions. Condition1- If all the values in Sheet2 Column C is Yes/NA then Output value should be YES in G5 with background green color and if any NO is present value should be NO and Red Colour in G6. Condition2- If all the values in Sheet2 Column C is No/NA then Output value should be NO in G6 with background Red color.

The two steps are to figure out a formula - try COUNTIFS - to calculate the "Yes" or "No" part and then apply Conditional Formatting to that cell. No VBA required. — Doug Glancy
– Doug Glancy, Commented Sep 26, 2013 at 19:35
Can you put across the formula please for the above mentioned criteria and where should I put that formula. Sorry I am not familiar with Excel formula and Conditional formatting. — sandesh
– sandesh, Commented Sep 26, 2013 at 19:39
@DougGlancy: We can only find true condition using COUNTIFS. I mean only YES, what if NO is present in that column. How to make a decission over here? I want to know whether only YES or any NO is present in that column. Then give out the output. I have edited my question accordingly. Please refer once more. — sandesh
– sandesh, Commented Sep 26, 2013 at 20:03
@sandesh: What if there are both Yes and No in the column and none of the Conditions are true?? — Gary's Student
– Gary's Student, Commented Sep 26, 2013 at 20:05

Gary's Student · Accepted Answer · 2013-09-26 21:42:57Z

Give this a try:

Sub YesNoCheck()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim rCheck As Range
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    Set rCheck = s2.Range("C1:C100")
    s1.Range("G5:G6").Clear
    If wf.CountIf(rCheck, "No") = 0 Then
        s1.Range("G5").Value = "Yes"
        s1.Range("G5").Interior.ColorIndex = 10
        Exit Sub
    End If
    If wf.CountIf(rCheck, "Yes") = 0 Then
        s1.Range("G6").Value = "No"
        s1.Range("G6").Interior.ColorIndex = 3
    End If
End Sub

EDIT#1

My error, use this instead:

Sub YesNoCheck2()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim rCheck As Range
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    Set rCheck = s2.Range("C1:C100")
    s1.Range("G5:G6").Clear
    If wf.CountIf(rCheck, "Yes") + wf.CountIf(rCheck, "NA") = 100 Then
        s1.Range("G5").Value = "Yes"
        s1.Range("G5").Interior.ColorIndex = 10
    End If
    If wf.CountIf(rCheck, "No") + wf.CountIf(rCheck, "NA") = 100 Then
        s1.Range("G6").Value = "No"
        s1.Range("G6").Interior.ColorIndex = 3
    End If
End Sub

A friend looking over my shoulder pointed out that if all the entries are "NA", then both conditions could be true!!

Collectives™ on Stack Overflow

Excel Macros: Checking Data and coloring using excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related