Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create a formula based on cell color
Hi,
I was wondering if there was a formula to add up cells based on the cell color? These cells have names in them and no numbers For example: Column A Column B white cell yellow cell white cell white cell yellow cell yellow cell I need the cells that are yellow to equal 25 and the cells with no color to equal 45. This spreadsheet is to total the pay for about 100 people and the yellow cells are not all in a column. Is there a formula along the lines of: if a person's name is in a yellow cell, then the dollar amount associated with the cell = 25 Sorry this is so long, I wasn't sure how else to explain it. Thanks for the help, Laura |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create a formula based on cell color
Laura,
there are very nice tools out there that you may want to download that will help your daily routines. Your problem can be solved by a VBA code were you would use : If cells's interior color = yellow (index here) then ajacent cell = 25 If you do not know VBA, get asap-utilities and this will do 95% you the job. Igor "pcwolfie" wrote: Hi, I was wondering if there was a formula to add up cells based on the cell color? These cells have names in them and no numbers For example: Column A Column B white cell yellow cell white cell white cell yellow cell yellow cell I need the cells that are yellow to equal 25 and the cells with no color to equal 45. This spreadsheet is to total the pay for about 100 people and the yellow cells are not all in a column. Is there a formula along the lines of: if a person's name is in a yellow cell, then the dollar amount associated with the cell = 25 Sorry this is so long, I wasn't sure how else to explain it. Thanks for the help, Laura |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create a formula based on cell color
Hi,
Add the following VBA code to a module in the Visual Basic Editor of your workbook: Function CountOfColors(myRange As Range, myCell As Range) As Long Dim Counter As Integer Dim cell As Range Counter = 0 For Each cell In myRange If cell.Interior.ColorIndex = myCell.Interior.ColorIndex Then Counter = Counter + 1 End If Next cell CountOfColors = 25 * Counter End Function Suppose that the cells that you want to check are in the range A1:D10, then in a cell outside this range color one of the cells whatever color the cells are that you want to find, in your example some shade of yellow. Suppose that the cell you color is F1, then you can enter the following formula in a cell: =countofcolors(A1:D10,F1) The beauty of this function is that it can work for any color. -- Cheers, Shane Devenshire "pcwolfie" wrote: Hi, I was wondering if there was a formula to add up cells based on the cell color? These cells have names in them and no numbers For example: Column A Column B white cell yellow cell white cell white cell yellow cell yellow cell I need the cells that are yellow to equal 25 and the cells with no color to equal 45. This spreadsheet is to total the pay for about 100 people and the yellow cells are not all in a column. Is there a formula along the lines of: if a person's name is in a yellow cell, then the dollar amount associated with the cell = 25 Sorry this is so long, I wasn't sure how else to explain it. Thanks for the help, Laura |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Condition font color in a single cell based on its formula data. | Excel Worksheet Functions | |||
Create formula that will pull a value based on text in diff cell? | Excel Discussion (Misc queries) | |||
How do I change color pattern based upon a formula within a cell? | Excel Worksheet Functions | |||
How do you fill a cell with a color based on IFTHEN formula? | Excel Worksheet Functions | |||
How do I create formula to change cell color | Excel Worksheet Functions |