This code colours the NEXT cell when a certain cell houses a value.
Private Sub CommandButton1_Click() Dim Rng As Range For Each Rng In Range("G2:C1417") If Rng.Value = 6 Then Rng.Offset(1, 0).Interior.Color = vbBlue Next Rng End Sub
So in this case if a cell has a value of 6 then the Next cell down is colour coded blue.
Here is my question.Is there a way I could write some code to count the number of newly coloured cells on the sheet and then print the total.This would save me having to go through long sheets counting manually.
i have a spreadsheet in excel 2007. It shows a students target grade in one column and their recent test mark in another column. Firstly i have applied conditional formatting to say whether or not the student has hit their target, below or above, using red, yellow and green colours. This all works fine.
Now i would like to add a formula that counts the number of cells that are red, yellow or green etc.....
I have a workbook (see attached) that has 2 sheets, Work and Holiday. I want to be able to enter a persons initials into the wrksheet 'Work' for each day of the week.
If that person is listed on the holiday sheet for that day as 'A' then the cell in 'Work' is coloured Red, if not then it is coloured Green. I have tried to get this to work using a defined list and various IF statements but all to no avail.
managed to count cells based on colour, however if the colour is variable due to conditional formatting then the UDF doesnt work. So my next thought is just to copy the colours into another column next to it and then get the UDF to count those colours. however copying just the colours is not as easy as seems. when i use format painter the colour all comes out the same (once again i am presuming this is due to conditional formatting). the macros etc seem to complicated for this is there a simple thing i am missing?
Having searched and read lots of posts, but without finding the answer.
Is it possible to creat a macro that toggles a cell between [empty], [x], [o] and [n/a], with the [x] cell having a background colour of green, the [o] cell having a background colour of red and the [n/a] with a background of yellow.
The above cells won't have anyother function as they will be used as indicators in a progress chart.
I have created a Vacation Calendar workbook with 6 sheets. All the sheets contain the 12 month calendar. Each row contains an employee and the columns are the days of the week. I have to keep track of 5 departments. I have 5 supervisors that are on different sheets that need to be included on the 6th sheet. I have set conditional formating to show that when I type "v" in a cell that is will go green. Is there a way to populate this information to another sheet without having to copy and paste?
I know the VBA code to count cells from a data set that were manually colour-coded.
The problem is that the code (pasted below) doesn't seem to pick up cells that were coloured via Conditional Formatting. How do I do this? What is the VBA (if there is one)?
Function COLORCOUNT(varRange As Range, varColor As Range) Dim cell As Range For Each cell In varRange If cell.Interior.ColorIndex = varColor.Interior.ColorIndex Then COLORCOUNT = COLORCOUNT + 1 End If Next End Function
a macro to sum up rows without coloured.That means the total qty from B11 to B17. For your information, the hightlighted rows vary from time to time. Any guide and help is greatly appreciated.Pls refer to my attachment.
I have a spreadsheet and certain cells are coloured to represent certain criterier, I would like to display all of one coloured cells to show status of that colour, say I want all the red cells locations to be displayed so that i can check the status.
I have a simple Excel 2007 column chart which has names listed along the y axis for each column. Associated with each name in a small table is a colour reference. Is it possible to add a small marker (say a triangle or something) that aligns with each column?
Or possibly (or in addition) colour each column differently and in accordance with the assigned colours? I'm doing this with some vba code. but if someone can point out the general principle I should be able to work that in.
column A = Date : 01/02, 07/02, 14/02, 21/02, 28/02 (x-axis : shows when the table is updated) column B = project: x, x, x, x, ,x (name of the project and trend line) column C = Delivery Date : 01/05/2009, 08/05/2009, 20/05/2009, 30/05/2009, 28/02/2009 (plotted on the graph) column D = Status : Green, Amber, Red, Green, Blue (status of the project. the points should be the same colour as is described in the table) column E = Comments: original, delay, supply, out of money, on track, delivered-wow! (these comments will pop up if the user holds the cursor over a point)
NB Y-axis scale : 01/01/2009 to 31/12/2009 with increments of 14 days. this will be the same scale used for all projects.
Date Project Delivery Date Status Comments 01/02 x 01/05/2009 Green original date 07/02 x 08/05/2009 Amber delay supply 14/02 x 20/05/2009 Red out of money 21/02 x 30/05/2009 Green on track 28/02 x 28/02/2009 Blue delivered-wow
So id like the macro to draw the line for project x based on the 'delivery date'. The points should be coloured according to the 'status' column and when you hover the mouse over the point the data lable will show up taking info from the 'comments' column.
Would it be possible to create a macro that will be able to generate this graph automatically. I have a few projects id like to do the same thing for.
Is there a function in Excel that will count cells that are different from one another? I.e. it would not count the same value twice. For example, in column A:
a a b a b b c a c
The result would be 3. However, if I replaced the first "a" with a "d" I would get 4 as a result. Basically I want to know how many different arguments there are in a list. I could not find a function that does this in the Excel list of functions.
I am trying to create a formula to count the # of names up to a certain word. So Cells A1-F1. Are a list of names, the Last name in the list starts with VACANT. How do I create a formula to count the # of names in the list upto the work VACANT? So if VACANT moves to C1 it would decrease the # of names.
I have written some VBA code to to test if there are 8 names within a range (EF5:FH5, these are 8 merged cells, (4x4 cells)), if there is 8 names within the cells, ie not any empty cells, then the coding will add the new staff name to "trigger_box_1b", and if not then "trigger_box_1a". These trigger boxes are then used to populate further cells.
Private Sub Trigger_Box_1_Change() Windows("Admin Skills Matrix.xls").Activate 'puts focus on correct spreadsheet Sheets("Skills Matrix").Select 'puts focus on correct page Select Case WorksheetFunction. CountIf(Range("EF5:FH5"), 0).double Case 0 Trigger_Box_1b.Text = New_Staff_Name Case Else Trigger_Box_1a.Text = New_Staff_Name End Select End Sub
the problems I have be having is that VBA is not recognising the Countif function ("Compile Error: Invalid qualifier", then highlights the Countif). I have tried using a CountA function with the same responce. I have tested the rest of the coding and know that it's working fine (don't think anyone would like to see 75+ pages of VBA coding)
I know this question may have been posted before, but I can not seem to get this to work with my formula. I have a formula to count the values of a column so that I can convert it to a graph. I am giving a value of 1 to cells that contain numbers between 0 and 9.5 and giving a value of 0 to cells that contain numbers greater than 9.5. It works fine except that I have some blank cells that I do not want to get rid of and it is counting those as zeros and giving them a value of one. I want to ignore the blank cells or give them a value of zero.
In my dataset above the cases finish after 12 months (hence why there is a maximum number of cases).
What I am seeking is a method to count along a certain number of cells, then sum the contents of those. For example, I would like to count the contents of up to 12 cells, then sum those 12 cells.
Where it gets difficult is as follows:
- In month 6, I am just counting the contents of 6 cells (months 1-6) - In month 12, I am counting the contents of 12 cells (months 1 -12) - In month 18, I am counting the contents of 12 cells (month 7 - 18)
way to count cells in a spreadsheet (not values in the cells but just the cells themselves). So I have a whole bunch of cells, some are red, some are blue, some are green based on the info i have to keep track of.
so can i write some COUNTIF for red cells green cells, etc