Bilkul!
Main aapko ready-to-post blog material de raha hoon — title + introduction + VBA code + explanation sab kuch ekdum clear tariqe se.
Aap ise direct apne blog pe daal sakte ho.

---

📄 How to Calculate Average Marks Based on Cell Color in Excel Using VBA

Introduction:
Sometimes while working in Excel, we use different cell colors like red, yellow, and green to visually represent performance or status.
But what if you want to calculate the average marks based on these colors automatically?
Manually checking each color is time-consuming.
Using a simple VBA function, you can easily assign scores to each color and calculate the average marks.

In this blog, I will show you a custom VBA function that does exactly this!

---

📌 VBA Code to Calculate Average Based on Cell Colors

Function GetColorMarksAverage(rng As Range) As Variant
    Dim cell As Range
    Dim total As Double
    Dim count As Long
    Dim redScore As Double, yellowScore As Double, greenScore As Double
    Dim r As Long, g As Long, b As Long

    ' Assign scores to colors
    redScore = 0
    yellowScore = 2.5
    greenScore = 5

    ' Loop through each cell in the selected range
    For Each cell In rng
        If cell.Interior.ColorIndex <> xlNone Then
            r = (cell.Interior.Color Mod 256)
            g = (cell.Interior.Color \ 256) Mod 256
            b = (cell.Interior.Color \ 65536) Mod 256

            ' Match color category using RGB values
            If r > 200 And g < 100 And b < 100 Then
                total = total + redScore ' Red
                count = count + 1
            ElseIf r > 200 And g > 200 And b < 100 Then
                total = total + yellowScore ' Yellow
                count = count + 1
            ElseIf g > 150 And r < 150 And b < 150 Then
                total = total + greenScore ' Green
                count = count + 1
            End If
        End If
    Next cell

    ' Return "NA" if no colored cells are found
    If count = 0 Then
        GetColorMarksAverage = "NA"
    Else
        GetColorMarksAverage = total / count
    End If
End Function

---

🛠️ How This VBA Code Works:

1. Function Name:
   GetColorMarksAverage(rng As Range) — This takes a range of cells as input.

2. Score Assignment:

   • Red colored cells get 0 marks.

   • Yellow colored cells get 2.5 marks.

   • Green colored cells get 5 marks.

3. Color Identification:
   The code reads each cell’s RGB (Red, Green, Blue) values:

   • Red color: High red value, low green and blue.

   • Yellow color: High red and green values, low blue.

   • Green color: High green value, low red and blue.

4. Average Calculation:

   • Adds up the scores of all colored cells.

   • Divides by the number of cells considered.

   • Returns the average marks.

   • If no colored cells are found, it returns "NA".

---

📋 How to Use This Function in Excel:

1. Press Alt + F11 to open the VBA Editor.

2. Go to Insert → Module.

3. Copy and paste the above code into the module.

4. Save and close the VBA Editor.

5. In your Excel sheet, you can now use the formula like this:

   =GetColorMarksAverage(A1:A10)
   

   (Replace A1:A10 with the range where your colored cells are.)

---

🎯 Example Scenario:

Suppose you have:

Cell	Color
A1	Green
A2	Red
A3	Yellow
A4	Green

Then:

• Green = 5

• Red = 0

• Yellow = 2.5

• Green = 5

Total = 5 + 0 + 2.5 + 5 = 12.5
Count = 4

Average = 12.5 / 4 = 3.125

So the formula =GetColorMarksAverage(A1:A4) will return 3.125.

---

✨ Final Words:

Using this simple VBA function, you can automate the task of evaluating colored performance indicators in Excel and save a lot of time!
This is especially helpful for grading, project tracking, or any color-coded reports.

---

✅ Pro Tip: You can also modify the score values (redScore, yellowScore, greenScore) according to your needs easily in the code.

---