Results 1 to 8 of 8

Search cells based on student name, cell background colour and return a tally for colour

Threaded View

  1. #1
    Registered User
    Join Date
    04-09-2013
    Location
    Australia
    MS-Off Ver
    Excel 2011
    Posts
    11

    Search cells based on student name, cell background colour and return a tally for colour

    At my school we're provided with national test data for entire year levels of students. Our task is to isolate some data for our own classes to hopefully improve student outcomes.

    I've attached a spreadsheet which shows the type of format we're presented with. I just don't know how to calculate tallies based on cell background colours for just my class.

    Explanation:
    I have a class summary worksheet which lists individual students in my class (generally up to 30 students). On this same class summary sheet there are some cells to show tallies of achievement levels. These show how the class performed at the national test in reading, writing and numeracy.

    The Year Level Summary worksheet has the results for the entire year level (usually over a hundred students). There are columns for student results in grade 3, 5, 7 and 9. This group only has results from grade 3 and 5.

    There are five possible cell background colours from green to red. I'd like to search through the hundred or so students, finding the students in my class and returning the tally of each colour for their reading, writing and numeracy (this would be from their grade 5 test as it was the most recent test).

    I've used the following formula to find colour tallies for the entire year level but I'm not sure how to produce a tally for just my class.

    =CountCcolor('Year Level Summary'!E9:E107,'Year Level Summary'!H113)


    The CountCcolor function (thanks to Raddini Rahayu):


    Function CountCcolor(range_data As Range, criteria As Range) As Long Dim datax As Range Dim xcolor As Long xcolor = criteria.Interior.ColorIndex For Each datax In range_data If datax.Interior.ColorIndex = xcolor Then CountCcolor = CountCcolor + 1 End If Next datax End Function

    I hope this makes sense and I'd really appreciate any help anyone can give.

    Thank you.
    Attached Files Attached Files
    Last edited by drof_06; 01-23-2016 at 08:35 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 01-05-2016, 06:36 PM
  2. Replies: 3
    Last Post: 02-13-2014, 06:31 AM
  3. Change colour of cells based on another cell's colour (Not value)
    By LTrain89 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-25-2013, 08:44 PM
  4. Replies: 7
    Last Post: 07-16-2012, 05:10 PM
  5. IF formula based on background cell colour
    By mr_teacher in forum Excel General
    Replies: 1
    Last Post: 11-28-2007, 10:44 AM
  6. [SOLVED] change a cell background colour to my own RGB colour requirements
    By Stephen Doughty in forum Excel General
    Replies: 4
    Last Post: 06-16-2006, 08:15 AM
  7. Changing Background Colour Based on Cell Contents
    By Sukh in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-12-2006, 09:00 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1