help for VBA conditional formating count on correct color

[VISHAL120]

I actually have one assessment file where we are entering the answers of multiple choice questions which are of A,B,C,D.

And I have place conditional formatting where we are checking the answers with the row 5 starting from column N to AQ.
If the answer is correct then the cell turns GREEN.
ON column AR we have to count the number of correct answers which are GREEN and placed it there like in the attached sheet.

After researching on the internet I have come with this code which works for the first row but on the below rows it keep displaying the same the answer rather than the correct answer. Also attaching the file for reference

Code:

Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range)
Dim Bambo As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Bambo = False

For CF1 = 1 To CellsRange.FormatConditions.Count
    If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
        Bambo = True
        Exit For
    End If
Next CF1

CF2 = 0
CF3 = 0

If Bambo = True Then
    For Each CFCELL In CellsRange
       dbw = CFCELL.FormatConditions(CF1).Formula1
       dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)
       dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1))

       If Evaluate(dbw) = True Then CF2 = CF2 + 1
       CF3 = CF3 + 1
    Next CFCELL
Else
    COUNTConditionColorCells = "NO-COLOR"
    Exit Function
End If

COUNTConditionColorCells = CF2
End Function

I will highly appreciate if I can get some guidelines for the correction of it so I can make work on the file please as actually we are counting this manually.

Also starting from the cell N7 to AQ65 it’s the grid where the answers will be input. I have put conditional format in it where if the answer is good then the cell turns GREEN and is not its turn RED.

But I see even if its blank its turning RED. If this also can be removed please so the conditional format works only when there are answers input but not when blank.

[SamT]

See Code comments below

Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range)
'Assume the interior of ColorRng is Green

Dim Bambo As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Bambo = False

'Does any FormatCondition set an Interior to green if condition is met?
'Conditions are unknown to this author
For CF1 = 1 To CellsRange.FormatConditions.Count
    If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
        Bambo = True
        Exit For
    'Else see Else below
    End If
Next CF1

CF2 = 0
CF3 = 0

If Bambo = True Then
    For Each CFCELL In CellsRange
       dbw = CFCELL.FormatConditions(CF1).Formula1
       dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)

       'Active Cell Location is undefined, However, move the reference in the cell's formula up to 1800 columns to the right of Active Cell.
       'I supect this is the error
       dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, ,  ActiveCell.Resize(CellsRange.Rows.Count,  CellsRange.Columns.Count).Cells(CF3 + 1))

       If Evaluate(dbw) = True Then CF2 = CF2 + 1
       CF3 = CF3 + 1
    Next CFCELL
Else
    COUNTConditionColorCells = "NO-COLOR"
    Exit Function
End If

COUNTConditionColorCells = CF2
End Function

[SamT]

My method is to use a UserDefinedFunction

Replace COUNTConditionColorCells with

Option Explicit

Function CountCorrectAnswers(CorrectAnswersRng As Range, ResponsesRng As Range) As Long
Dim i As Long
Dim A As Long
A = 0

For i = 1 To CorrectAnswersRng.Count
    If ResponsesRng.Cells(i) = CorrectAnswersRng.Cells(i) Then A = A + 1
Next i
CountCorrectAnswers = A
End Function

And use the Formula "=CountCorrectAnswers(N$4:AQ$4, N7:AQ7)" Note $symbols in first Range Parameter. Copy the formula down as far as needed

[VISHAL120]

Hi SamT,

yes if the conditions are met then the color is change to Green

[VISHAL120]

i will this one a try and revert to you. Thank you again for your precious time

[VISHAL120]

Hi SamT,

Thank you the formula works. Thank you for that.

Concerning the condition formula where even when cells is blank is becoming RED in color. can we prevent that please.

thank you again for the formula. I will test it on the main file again and inform you the outcome.

[VISHAL120]

Hi SamT,

Thank you the formula works. Thank you for that.

Concerning the condition formula where even when cells is blank is becoming RED in color. can we prevent that please.

thank you again for the formula. I will test it on the main file again and inform you the outcome.

By the way am really impressed with the code . i am trying to understand the logic behind it.

Can you please elaborate it how its working. As this can help me understand the code well and even our colleague in this forums as well.

But frankly hats off.

[SamT]

I used cell O9. Remove/delete Condition 2
Condition 1 =O9=O$5 : Green
Condition2 = =AND(O9<>"",O9<>O$5) : Red

Leave empty cells uncolored: Empty is empty, leave the color empty

[VISHAL120]

HI SamT,

Thank you its clear and working properly on the main file. Solved