Return a value "IF" a cell is a certian colour...
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Return a value "IF" a cell is a certian colour...

  1. #1
    Guest

    Default

     
    Good Day All,

    I am a beginner Excel user who has been assigned a daunting task.

    My spreadsheet will be used to calculate course cost per division (I have 5 divisions).

    I have colour codes cells to denote which division a student is with in A4:A30. I have entered a cost for the course in I14.

    In a cell I want to calculate how much each division owes based on the number of each coloured cell in the column multiplied by the course cost.

    Any simple ideas (I'll even take more advanced ideas as I feel I'm getting the hang of some oft his stuff).

    Thank you all so much for your consideration!

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    You could insert this function in a standard VBA-module.
    ex.
    If you format a reference cell red this function will sim all red cells in an area.
    =colorsum(area, A1)

    Function ColorSum(rRange As Range, rColor As Range) As Double
    Dim rCell As Range
    Dim dSum As Variant
    dSum = 0
    Application.Volatile
    For Each rCell In rRange
    If rCell.Interior.ColorIndex = rColor.Interior.ColorIndex Then
    If IsNumeric(rCell) Then dSum = dSum + rCell.Value
    End If
    Next rCell
    ColorSum = dSum
    End Function

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com