Sum Cells By Colors
Mar 26, 2008
I am trying to create a Holiday/Absence Sheet that will count up hours of Holidays, absence, business Visits etc etc.
The only way I can think of doing this is by colour and filling the cells with the Number of hours holidays, Absence & Business Visits but to do this I need to create a formula that will sum by colour. I've tried a variety of things and I'm sure this can be done using VBA but it's something I have never used so I am at a loss.
So, what I need is a SUM (A1:A31 if colour= Red) and SUM (A1:A31 if colour= Blue) etc.
I hope this makes sense or one of you geniuses out there can come up with somethign smart that works better than my idea!
View 6 Replies
ADVERTISEMENT
Jul 7, 2013
My pallet lost color-coding - if I hover over each little scare it displays the names for the colors and if I click on them they color the cells with the right colors, but the palette itself lost the visual display of colors except for 8 colors: black, blue, red, magenta, yellow, cyan, and white.
I use color-coding of cells a lot and I find it difficult to work without visual clues. At least the hover-support allows me to get the work done, but with difficulty.
View 12 Replies
View Related
Sep 15, 2013
How do I go about formatting selected cells for different colors for each different number in the selected cells? If I have numbers scattered about I want the cell to show a different color depending on the number that is inserted. EG: 115=red, 365=blue.
View 2 Replies
View Related
Mar 13, 2012
I have a worksheet where I want the to change the collor of a range of cells in accordance to the value in another range of cells.
So if the value is "Gron" in cells C4:C27 then I want cells J4:J27 to return the color green. If, however, the value is "Rod" in cells C4:C27 then I want cells J4:J27 to return the color red.
View 9 Replies
View Related
Sep 24, 2009
I'm trying to get a range of cells to change colors. This should happen from VBA code and happen on specified sheet that is not the default sheet when the userform appears that has the button that triggers the event. I have to reference the range with variables because it changes depending on other events.
If I put in: ....
View 9 Replies
View Related
Jan 6, 2013
I'm trying to create a formula that can display colors based on the strength of the margin of victory of a candidate, take these two examples for instance.
County
Frank
Cole
Dean
Total
MOV
MOV%
[Code]....
I want to be able to draw in the outline of these counties (or at least the cell proceeding the MOV%) displaying the color that corresponds with the winner of that county. The added twist would be to shade the color depending on the Margin of Victory (MOV) as well. The closer the vote, the lighter (or mixed?) the cell (or county) would be. If it could be at all possible I would really be cool to mix both colors so that a result 100% for one candidate would get a color 100% pure while a 17% MOV would get a color 17% more of the winning candidate than of the loser (or losers) colors. I don't think a VBA is necessary for this one.
View 2 Replies
View Related
Feb 27, 2013
I am trying to format certain cells to change colors depending on the date and the content of the next field to show due dates.
cell label
due date
date completed
c5
au5
be5
c6
au6
be6
c7
au7
be7
c8
au8
be8
What I need is for the contents of au5 to be green up until 10 days before the due date, yellow up until 5 days, and red with less than 5 days until the due date. If the date completed is entered into be5, then the contents of au5 should be black.
Is this possible, and if it is, how do I set this up? I tried to use
=$AU5-TODAY()>=10
And choose green formatting (something I found on yahoo answers), but when I enter the date the text is still the original black font.
View 5 Replies
View Related
Feb 22, 2010
using =ColorFunction to count cells highlighted in different colors.
Is that function available in Excel 2007 under a different function name?
View 9 Replies
View Related
Jan 3, 2014
How to sort out a way to count cells whose fill colors have been set via conditional formatting.
I'm sure that I was once able to use Chip Pearson's CountOfCF function back in the days of Excel 2003, as it worked around the 3 condition limit and used .ColorIndex which assumed that you'd only ever be formatting using the .ColorIndex values of 1 to 56, but I can't seem to be able to get it to work in 2010.
View 7 Replies
View Related
Mar 22, 2008
I'm a average excel user, i know my way around it and kow common and basic formulee, VB isnt a strong point, but i am willing to attemp anything for this, let me explain
Lets say in colum A I have a list of items, in colum B i have a check box, currently the check box is red, if i tpye a x it turns green due to conditional formating. That works fine...onto the problem...
Lets say in colum A i have my items but they are repeated...eg A1, A145, A166, A122 are all the same item, what i want is regardless of where i place the x each check box under coloum B either gets a X and turns green, or just turns green, i dont mind...
I dont think conditional formatting can cut this, though i may be wrong. Ive tried to use the IF function under condtional formatting but ran into problems...
View 9 Replies
View Related
Mar 28, 2009
Is there a way of getting more colors in Excel than what is shown on the standard pallet.
View 3 Replies
View Related
Dec 8, 2011
How to change the color of a single tabe. What I'm trying to do now is reset the tab color for all tabs.
Here is my code:
'REMOVE TAB COLOR
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
With ActiveWorkbook.ActiveSheet.Tab
.ColorIndex = xlNone
.TintAndShade = 0
End With
On Error Resume Next
Next ws
I'm new to using loops so I'm not exactly sure what the above code is doing. When I debug it goes through the steps without any errors but it doesn't actually change the tab colors.
View 3 Replies
View Related
Nov 15, 2007
The following code is the code "behind" the run button on my userform. The last section reads in a column ( of numbers and text) into a listbox based on the users choice. Is there any way I can have only certain rows in a different color - based on either if the value in that row is greater than a certain number of if the text in that row is a particular sentence?
Private Sub CommandButton1_Click()
'Dimension all variables
Dim fqc As Long
Dim eptg As Long
Dim newlabel As String
Dim BorC As String
Dim now As Long
Dim secondorthird As String
'Collect User Choices
fqc = TextBox3.Value ' Final Quiz Choice
eptg = TextBox5.Value ' Expected Post Test Grade
BorC = TextBox4.Value ' Is desired grade a B or C
now = TextBox6.Value ' Number Of Weeks after term has started
secondorthird = "second"
If (now = 3) Then secondorthird = "third" .....
View 9 Replies
View Related
May 4, 2007
For some reason colors will not show on any EXcel doc. When I type colored text in a cell, it shows the color until I press enter, then it goes black. Cell fill colors do not show at all.
However when I send the doc to another computer the colors wil show.
View 6 Replies
View Related
Nov 27, 2013
I have a big set of data and I want to create different graphs in different tabs with it.
So Imagine that in Tab 1 I want to do the sales forecast for the next five years for product A for all countries in Europe
Tab 2 would be the same, but for product b
Tab 3 the same, but for product c
So with dynamic tables in each tab I can easily do my graph
The problems is that since values vary per product country A in tab 1 is red, in tab 2 is blue and in tab 3 may not exist.
How can I set up a template so taht I do not have to color code each country in each tab? I.e. I want Spain to be blue in all my graphs in the workbook, Italy red and so on...
View 1 Replies
View Related
Jan 12, 2009
When a workbook is opened, (using OnOpen() ), I trying to get two header titles colors (A2, B2) to change from their usual Black to Red to Black to Red and finally back to Back.
I'm battling, especially with the time (1 second) for each change. I'm probably using the wrong approach. Can anyone suggest a sound method to achieve this?
View 6 Replies
View Related
Nov 22, 2009
i want to know how to mark the cells with color where the starting date is not greater than ending date.
View 4 Replies
View Related
Jan 2, 2009
I need to change to shift the colors to the left instead of the right.
View 3 Replies
View Related
Apr 3, 2012
Trying to format cells b9 through b 24 and cells c9 through c24 to change from white to a light grey color by clicking the mouse button once. so one click white, another click grey. The color grey I need is white, background 1, darker 25% in the autofill field in excel. I have search other forums they all lead to changing the code, I did this already. just copy pasted a code I found but all cells were selected to change a bright green.
View 3 Replies
View Related
Jun 27, 2012
I am making some grids which will have a series of numbers in the individual cells. But I can't figure out how to change the colors of the digits. If I change one digit to Red; all the numbers in that cell change to Red. Whereas I would like to have them as Red,Green,Black,Blue,etc.
View 3 Replies
View Related
Jun 25, 2013
I'm trying to update a workbook with a new look and want to change the cell color based upon what the cell color currently is.
I'm having problems with the code that I created. I just can't seem to get the syntax correct for the code.
For the code below, my Range of A5:D5 is just a test range where each cell should change colors. When completed, my range will be much greater.
Code:
Sub ChangeColors()
Dim Cell As Range
For Each Cell In Range("A5:D5")
[Code]....
View 4 Replies
View Related
Dec 15, 2007
I have 2 cells (B2 and C2) with date format, showing dates from a calendar.
FYI, These cells are NOT in my Calendar.
Now, I need to write a formula for my calendar cells to check these two cells (B2 and C2) and change the color of the cells inside my calendar according to the date sindicated inthese two cells (B2 and C2).
I did this with numbers using conditional formating and it works perfect but doing this with dates is giving me a hard time. Is there a way to do this? below is a link to my excel file.
http://www.box.net/shared/si7g77l0b8
View 9 Replies
View Related
Feb 19, 2009
I am trying to create a formula that looks at a spreadsheet containing training percentages for employees. I need to look at these percentages and based on the date that the person started I need to look at certain rules. First it needs to look at what has happened more recently , moving to a new department or promotion. Next it needs to use that info to decide which rule to use. Ex. the person started 90 days ago then they are subject to rule A. The training percentage then needs to be looked at to see what rating they have. EX. percentage < 90% but > 75% gives a rating of 1. percentage than 50% gives rating of 2. percentage < than 50% rating of 3. Rule B would contain different criteria. My problem is that I tried nesting IF statements but I need more than 7. When I tried to do this in VBA I couldn't get it to work.
View 9 Replies
View Related
May 23, 2009
Is there any way to have more colors in conditional formatting since it only allow maximum 3 colors. Anyway for your info I have an attendance sheet which I want the data to change colors every time the data is enter as below :-
MC / EL / NS = RED
Y = BLUE
AL = GREEN
OD / RD = YELLOW
PH = BLACK
The data range is G6:AK82
View 9 Replies
View Related
May 23, 2009
I have read the instructions for conditional formatting and I don't think that is what I need. I have 10 diff year 2005,06,07 etc. I want each cell with with a diff year to turn a diff color. Like I want 2005 to be red and 2008 to be blue.
View 9 Replies
View Related
Jun 10, 2009
I have a list of number (these numbers are related to incidents) and I want to use the numbers as a basis to split of the spreasheet by coloring the rows. There is no order to how many of the same number there is. So, what I would like to do is fill rows with a color based on the numbers. For example.
My list (part of it) looks like this: ...
View 9 Replies
View Related
Nov 8, 2006
I've worked out the following to change the tab color of a "sub" sheet when data on a master sheet is changed. Could anyone recommend the best way to apply this to multiple sheets (20+) without writing an "if" statement for each "sub" sheet?
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Worksheets("Sheet2").Range("E7").Value <> 0 Then
Worksheets("Sheet2").Tab.ColorIndex = 4
Else: Worksheets("Sheet2").Tab.ColorIndex = xlColorIndexNone
End If
End Sub
The master sheet is a bill summary where totals are added, if one of the line items is a subcontracted item the total on that particular subs sheet changes from "$0" and i would like the tab color to change on any sub sheet that is affected by the current quantities billed.
View 5 Replies
View Related
Mar 17, 2007
I'm trying to have change if the date on it is passed "now()". I am already using the 3 conditional formatting fields and need this one and another one. Here are 2 problems I seem to be having.
First - the code below only works if I change the date on the cell. I want code to either work with Worksheet Active or any other way so the user does not have to redo the date’s everyday.
Second - a record might have conditional formatting already. Is it possible for the target cell in this code can show this color while the rest of the row shows the color of the conditional formatting?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("G1:G2000")) Is Nothing Then
Select Case Target
Case Is <= Now()
icolor = 39
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
View 7 Replies
View Related
Jun 12, 2007
I have an interesting dilemma. I have Microsoft Excel 2003 and in one workbook I have a choice of certain colors for autofill and for font colors. In another workbook I have a DIFFERENT pallete of colors to choose from. Both workbooks are in excel 2003. Is there a reason why one workbook has one set of colors and another does not? Also, is there a way to change the second workbook so I have the same set of fill in colors in both?
View 2 Replies
View Related
Jul 31, 2007
It is working, but the problem It doesn't change the color number automatically after I change the color. There is any way to set up that option so can change automatically?
Function CellColor(rCell As Range, Optional ColorName As Boolean)
Dim strColor As String, iIndexNum As Integer
Select Case rCell.Interior.ColorIndex
Case 1
strColor = "Black"
iIndexNum = 1
Case 53
strColor = "Brown"
iIndexNum = 53
Case 52
strColor = "Olive Green"
iIndexNum = 52
Case 51
strColor = "Dark Green"
iIndexNum = 51...................
View 7 Replies
View Related
