VISHAL120
04-08-2021, 05:41 AM
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.
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.