I wish to count the "number" of numbers in a cell i.e.
Cell A1 has the following data: 12 25 25
This should return 3
Simillarly 4546546545646456 1 2
Should also return 3
I will have data such as the above in a row so i need the count to be able to count a range as opposed to the single cell example above.
Furthermore I need a separate count to count the number or red numbers in a cell. Same parameters as above.
My Request is in Two Parts.
Firstly, I would like to be able to List ALL the Colours Numbers in One Column & the Corresponding Colours Next to them Please in a Sheet Named "Summary" & Starting in Cell T3.
Secondly, I would like to Count How Many Cells in a Range on a Sheet Named "Network Points" and in Cells F4:Q179 has a Particular Colour Please. I would like the Totals to go on the Sheet Named "Summary" Starting in Cell W3.
I've got a function called countcolor (which I take no credit for) which counts a particular color in a range.
---------------------------------------------------------------------
Function CountColor(rColor As Range, rSumRange As Range)
Dim rCell As Range
Dim iCol As Integer
Dim vResult
iCol = rColor.Interior.ColorIndex
For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = vResult + 1
End If
Next rCell
CountColor = vResult
End Function
I have then set up a couple of macros which, when ran, fills a range of cells with a particular color. These colors are closely related but as you can see from the two macros below they ARE different. I'm finding my countcolor function is counting them twice though even though as you can see the color values are different.....
---------------------------------------------------------------------------
Sub pegcharged()
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 25600...........................
I'm sure this will be an easy query.
These are marks scored by 5 people in 10 exams
Name of the student
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS[code]....
Range
A1 to K6
In each row top 5 MARKS should be filled with BLUE,VIOLET,YELLOW,RED AND PINK
If its not possible. Top 3 with dark colour, remaining 2 with light colour using conditional formatting
Count formula. I have few cells highlihgted in different colours based on the conditional format. Is there a count formulat to count the numbers of cells based on the colours.
For Example - I have 3 cells highlighted in green, 2 in red etc. I need count formula to count all green cells and count red cells.
The idea came about after wanting to show a 'gold' 'silver' and 'bronze' background colour, but having to plump for yellow, grey and orange looked a little crap :f
If not, can a workaround be created by using code and a picture or 2?
Is it possible to create a formula that looks at a cell (say A20) and if TRUE changes the colour of A22 to green if false to red?
View 9 Replies View RelatedAttached is a table detailing top 20 Incidents and Requests. Requests are highlighted in Yellow and Incidents are in Green. Associated with this table is a graph. What I like to do is if the bar on the graph represents a Request I want the colour to be yellow and if the bar represents an Incident I want it to be green. At the moment I can only choice one colour or a different colour for each bar.
View 4 Replies View RelatedUsing 2007, I need to conditionally format a cell colour based on 5 other cell colours that have been conditionally formatted. The 5 other cells will be coloured either red or greem. What I want to do is have an overall status cell that would be show green if all of the other 5 cells were green, amber if 4 of the other cells were green and one was red and red if 3 or more cells are red.
View 4 Replies View RelatedI have a vertical list where every other cell is coloured grey. The values contained in the list are regularly sorted into alphabetical order with new values being added and other being removed.
Is there a way to keep the cells alternate grey & white after I have sorted them? Currently, when I sort, if a cell is coloured grey and is moved down one cell the colour is moved and not just the values.
was looking if you could change a row of cells background colour if certain word is inputted elsewhere. in b2:f2 is data- was looking if, could these cells background colour change when yes is entered in G2? does not matter which is used, VBA or con formatting just cant work it out.
View 9 Replies View RelatedIs it possible to select only cells of a certain colour, then change these cells to a different colour? example
I want to make the dark grey columns a light green and the red columns a lets say yellow and the text black. Is this possible??
does anyone know of a way to amend this to add the cell to a range say "All_blue" to then copy and paste special values.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) ...
What I need to do is have a cell that will be say yellow until there is information put into this cell. The information could be in the format of text or numbers. The information would not always be the same so it would need to be yellow when there is no information in the cell and another colour or white when there is information in the cell.
View 6 Replies View RelatedThis question about conditional formatting appears to be easy. Lets say I have a column like the following, like I posted somewhere else: (not very relevant for this)
BGW
3UG
WWU
1BR
UUBRG
WUBRG
7UUGGRRR
How do I have all characters in this column be coloured accordingly to:
W = WHITE
U = BLUE
B = BLACK
R = RED
G = GREEN
So that 7UUGGRRR would automatically switch to:
7UUGGRRR
What I already tried:
- I applied conditional formatting to the column (actually, the cells interval, [C2..C8]) creating the rule: 'format only cells that contain -> specific text' -> then I added "U" as the text and told it to colour blue. As I predicted, this didn't work; the entire cell containing a single "U" will get blue, which is unwanted.
I've got 12 months worth of business accounts which have been downloaded onto a single spreadsheet.
These show negative and positive values. As I only want to work on the negative figs at the moment, how do I highlight the colour of the positive value cells or font to prevent any mistakes ?
i am pretty good with VBA after reading a lot of information on the internet but i have a problem i cannot solve:
I have two workbooks with the same sheet names and I am trying to transfer the interior colour of cells from one workbook to the other...
this is the code i came up so far but it doesn't work
With Application.Workbooks(WB1).Worksheets
For Each ws In Application.Workbooks(WB2).Worksheets
On Error Resume Next
For Each cl In ws.UsedRange
WB1.ws.cl.Interior.ColorIndex = WB2.ws.cl.Interior.ColorIndex
Next cl
Next ws
End With
I'm creating a project calendar in Excel and for each task I have a Start Date (A) and End Date (B) in adjacent cells. To the right are corresponding date cells like a timeline set up with 52 cells representing weeks of the year. I.e Week 1 (C) starts at 02 Jul 12, Week 2 (D) starts at 09 jul 12 etc. up to 24 Jun 14 (BF).
Start Date
End date
02-Jul-12
09-Jul-12
[Code]......
When a start date and end date are entered in the respective columns, I need to have the color start on the cell for that range in the timeline and that color should carry out to the end date cell in the timeline. A bit like a Gantt chart but not to that detail.
In my rows, I have 0, -1 and 1 and wanted to know how many 1s, 0s and -1s I have. So in the example below, there are 4 -1s, 4 0s and 3 1s.
-1
1
0
-1
1
0
-1
1
0
-1
0
Is there an easy way of counting odd or even numbers in a row? I can't figure it out on countif, isodd, odd, count etc.
View 2 Replies View RelatedI am trying to find a way of counting decimal numbers if, say, they begin with 3.
For example, I might have 3.33, 4.1, 3.0, 5.65, 3.8, 3.7, 3.33, etc.
I want to count anything that begins with 3 (3.33, 3.0, etc). Using the data above the answer would be 5 ...5 numbers that begin with a 3.
If this possible?
There doesn't appear to be the facility with Countif. I don't want to truncate the data (the spreadsheet already is quite extensive). I have tried using =SUMPRODUCT((Y$2:Y$170 >=3)*(Y$2:Y$170<=3.9)) but this will return an answer only when there is a complete set of data within cells within the range.
I use the following formula to count duplicate random numbers on Row 35 that occurred on Row 34:
=SUMPRODUCT(--(D35:I36<>""))-SUMPRODUCT((D35:I36<>"")/COUNTIF(D35:I36,D35:I36&""))
I would like to continue to compare against Row 34 and count duplicates on other rows (ex. Row 36 or Row 37) one row at a time.
In addition is it possible to visually identify the duplicates through conditional formatting (ex. Bold or color change) or other ways?
I need a formula that will count the number of consecutive 3 0's from the following Data series. There are 22 such events.
0
6
15
[Code].....
i want Counting duplicate numbers in a string for example Counting 2
22241278-------- Count(2)=4
Is there a formula I can use to count the number of unique claim numbers in an excel column?
Is there a way that I can format a cell containing a number containing a decimal point to a number without a decimal point. Example, 205.55 to 20555
I have 2 Columns. One column represents calendar dates and the other column represents numbers between 0 and 7.
Therea re 10000 rows in this table.
I would like to count how many consecutive days I observe certain numbers numbers ( i.e 3+, 4+, 5+, etc)
I have a column that contains numbers relating to processing days. I need formulas that I can put into a results table that will show me processing timescales. I need to count up the number of 0s, 1s, 2s, 3s....9s and any numbers that are 10 and above. The column also contains #Value! errors and any formulas I have tried won't work with the error code and are also counting blank cells as 0 whereas I only want to count the cells in which 0 was typed in.
in a2:a277 i have numbers 1-10, randomly. in m2:m277 i have numbers 0-4, also randomly. i want to count how many of the 1s in column a have a corresponding 0, how many have a 1, how many have a 2, etc. if you just give me one formula i can manipulate it for all 40 outputs i need,
View 9 Replies View RelatedAny way to make a formula that would "COUNT" the number of positive values from 0 to 999 (no negatives and nothing higher than 999). Another way of saying it would be values greater than or equal to 0 but not more than 999?
For example. If you had the following numbers in a list, I would like a formula that would only count the 3 numbers that are less than 1000 but equal to or greater than 0.
$370 $1,070 $1,400 $300 $1,160 ($4,720) $1,080 $1,170 $30 $1,050 $1,150
First, I have to say that I am very weak in VBA.
I have a section of code where I need to count the cells from J through BF in rows 42 through 76. The code snippet I'm working on is:
Code:
For r = 42 To 76 Step 1
if Application.WorksheetFunction.Count(Range("J" & r:"BP" & r)) = 0 Then
Err = Err + 1
[Code]....
The code checker highlights the ':' in the 'Range .... = 0' with the message "Expected: list separator or )".