Count Cell Colour On Conditionally Formatted Cells?

Aug 5, 2014

spread sheet that I need to do for work. Unfortunately I do not have excel on my home computer to be able to attach a spread sheet

The spreadsheet has one column (A) with a list of questions. The column next to this (B) has either a red or yellow cell in each row (a red cell would be a high risk to the business if the answer to the question in that row was no, and a yellow cell indicates a moderate risk to the business in the answer to the question in the row was no).

The third column (C) is conditionally formatted so if a 'y' was placed in any of the cells they would turn green. If 'n' is placed in any of the cells the cell would change to either red or yellow (this would depend on what the colour was in column B).

I need to know a formula to count cells by colour. So the number of red, yellow and green cells in column C would be counted automatically into a totals box for each colour at the bottom of the spreadsheet. I have tried some online suggestions but couldn't get these to work for cells that had been conditionally formatted. I'm not the most experienced person with spreadsheets (this time last week I couldn't add two cells together)

The final thing I need from the spreadsheet (and I'm not even sure if this is possible) is for a total box to be colour co-ordinated based on the number of red, yellow and green cells in column C. I would need the total box to be green if all column c is green, yellow if three or less cells in column C are yellow and red if any of the cells in column C are red or there are more than 3 yellow cells in column C.

View 2 Replies


A Cell Colour Based On 5 Other Cell Colours That Have Been Conditionally Formatted

Jun 30, 2009

Using 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 Related

Selecting Conditionally Formatted Cells That Have Been Coloured?

May 1, 2014

I am looking to produce a Macro to select conditionally formatted cell's from a worksheet, i got as far as selecting those cell's but i need it to only select cells that have been filled.

View 6 Replies View Related

Conditionally Formatted Cells Which Turn The Text In Them To Red If The Value Is Less Than 40

Aug 18, 2009

I have a series of conditionally formatted cells which turn the text in them to red if the value is less than 40. This works fine.

Occasionally however a value of less than 40 will need to be entered along with the letter 'v'.

ie. 39v

I'd still like this to be coloured red, but it's obviously coming out as black.

Is there a way to sort this out?

edited to add : Im actually using a separate cell to enter the value 40 (as the value can change).

View 7 Replies View Related

Clearing Cell Contents If Conditionally Formatted

May 3, 2009

This is my first use of the forum as I only joined yesterday after a recommendation from another colleague.

I am attempting to write a macro to clear the contents of cells in a range that have conditional formatting. Below is the formula I have used...

Sub clr()
Dim r As Range
For Each r In Range("09:050")
If InStr(r.Interior.ColorIndex, "40") Then r.ClearContents
Next r
End Sub

While the formula runs without error, it does not clear the contents, although I read on another thread on this forum that you cannot clear contents of cells that are conditionally formatted.

Alternatively, if there is a more efficient way to clear cell contents (without using a formula in the cell itself),

View 9 Replies View Related

Determine What Excel Color Integer Is For Conditionally Formatted Cell?

Oct 22, 2013

pivot table?

Is there a Color Function type vba code that can give me the color integer? I have one that can do that for cells that I've manually colored but I can't find a code that works on conditionally formatted cells.

View 1 Replies View Related

Sum Cells That Are Formatted Certain Colour In Excel?

Feb 1, 2014

Just wondering if it possible to sum cells that have been highlighted in yellow in an excel spreadsheet? I currently use Office 2007 and 2010.

View 4 Replies View Related

Bar Chart Which Counts How Many Cells Are Formatted As A Colour

Feb 17, 2014

How do I write a bar chart which reads the format of a cell instead of the content? ie I want to count how many cells in each column are amber. Amber cells have already been decided by conditional formatting.

See demo attached : bar chart demo.xlsx‎

View 6 Replies View Related

Copy Or Link Formatting From Conditionally Formatted Cell To Another Cell?

Aug 12, 2014

I have a column who's content is determined via about 6 nested if statements from data on that row. That cell is then conditionally formatted to a certain color based on the text that is ultimately printed from the nested ifs (simply an extra visual legend for the text). All of this contributing info and about 1000 lines items make a very large and difficult to print page. What I am trying to do is a make a summary sheet that simply takes the index number of these 1000 rows and copies or links the conditional formatting of the mentioned cell onto this number on another sheet. I have already linked this status cell and put it adjacent to the index number which works well in that with two columns I can show the index and status but if I could combine the color of the status cell onto the index cell, it would be even better. I am pretty sure another conditional format for this summary sheet would not be possible or be extremely complex since the contents of the index cell I am conditionally formatting have no bearing on the conditions for the format. Was hoping there is some VBA magic that could simply mimic the conditional formatting from one cell and put it on another.

View 1 Replies View Related

Linking Object Color To Conditionally Formatted Cell Color In Microsoft Excel

Aug 31, 2012

I have a range of cells that change colors with conditional formats based on the cell value from high to low. I would like to link the cell color to an object such as a circle or rectangle. When the cell value changes along with the conditional format, the color of the object will also change.

View 3 Replies View Related

Counting Conditionally Formatted Colors

Dec 10, 2007

I have columns that have different colors, depending on there conditional format i.e. Green or Red. I want to count the number of either Red or Green in a total at the bottom. I have search and found a few answers

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

BUT these don't seem to work on conditionally formatted columns

View 4 Replies View Related

Count Formatted Cells In A Row?

Dec 8, 2012

Is there an excel formula to count the formatted cells for each row?

Here an example;




01 05 31 34
10 18 21 28
23 26 33 36
06 11 37 39
15 19 25 30
16 17 35 38
02 03 08 09
04 12 27 29
13 14 24 40
07 20 22 32

[Code] .........

View 4 Replies View Related

Excel 2003 :: How To Count Cells That Are Formatted In RED

Jan 20, 2012

How to count those that are formatted in RED or bold using Excel 2003 as I know in later versions that is possible but old ones I am unsure how to count them.

View 1 Replies View Related

Counting Conditionally - Count A Number Of Cells In One Column?

Dec 11, 2012

I am attempting to count a number of cells in one column so long as they fall within a certain date, which is located in another cell in the same column. I'm trying to account for a number of reports that happened within a certain week.

The formula I came up with was


But this returns 1 more then it should no matter what. Even when all cells within A_DATE are empty. it returns 1.

I know the COUNTIFS portion works to count the cells between certain dates.

Is what i'm looking to do possible or am I suffering from Excel burnout and just can't see that logicaly it makes no sense?

View 2 Replies View Related

VBA Code To Colour Certain Cells Based On RGB Colour Shown In Cell A1

Apr 17, 2014

I have a lookup that gives and RGB colour code in Cell A1; for example 186, 206, 140.

I'd then like Cells R10:V15 to fill with the RGB colour based on the result in A1.

View 2 Replies View Related

Count For Different Colour Highlighted Cells?

Nov 8, 2013

i want to take the count for different colour cells in the excel. ex, if green colour in any particular cell, it has to take only green colour count & same like yellow & so on...

View 5 Replies View Related

How To Count Colour Cells In A Document

Sep 10, 2012

I need a formula to count coloured cells in a document, the 3 colours I have are: blue, yellow and green.

View 3 Replies View Related

Count Cells With Specific Infill Colour

Dec 10, 2012

I use an excel holiday planner.

Holiday days are infilled red.

How do I count the number of red filled cells at the end of each line?

View 6 Replies View Related

How To Count Conditional Format Colour Cells

Dec 27, 2008

Below I have an example from A1 to V1 which have 3 conditional formatting
conditional format 1. if value is 0 then colour red and font white
conditional format 2. if value is from 1 to 10 then colour blue and font white
conditional format 3. if values 11 to 25 colour green, font black


From the table above I want to count the cells which are in 3 different colours.
Result: Red=6 cells
Blue=7 cells
Green=9 cells

View 9 Replies View Related

Count The Number Of Cells With A Particular Colour AND SPECIFIC Text?

Jan 15, 2014

I have a spreadsheet where i would like to 'count' the number of cells with a particular colour AND SPECIFIC text (not 'any text').

I attach a xls with the initials of the person in column A, their colour as seen in cells in next four columns with the particular text in each cell. Therefore, for 'ABC1', in the given range, I would like to count how many cells have been allocated with the particular colour (brown, do not know colour index) and the particular text (1 or 2 or 4 or 5) in the range A1:CK39. Haven't supplied the actual sheet for confidentiality reasons.

View 7 Replies View Related

Count Only Based On Same Cell Colour?

Mar 20, 2013

I am trying to establish whether there is a excel formula which will allows you to just to count the cell with the same cell colour. I have made the below listed example to illustrate what I am trying to achieve:

A B C D E11/03


In the there are total of two green colours, and one red colour to be counted.

View 3 Replies View Related

Conditionally Set Of Cells Based On Different Cell?

Jan 10, 2012

Is it possible to conditionally a set of cells based on a different cell? Example; Background color of cells A2:A10 = blue if A1>0

View 1 Replies View Related

Count Cell In Colour Background (Set By Conditional Formatting)

Mar 25, 2013

I have been assigned a task to create one table by counting how many cells are in red, yellow, blue, white colour.

red: indicate the certificate which will expired within one month

Yellow: indicate certificate which will expired within 2 to 3 month

blue: indicate certificate which expired

white (without any colour): indicate certificate that will expired in the future which more than 3 month

The table shall consist of "non-expired cert" - red and yellow; expired cert and total cert.

View 6 Replies View Related

Count The Number Of Records Conditionally

Sep 15, 2008

Is there a formula that I can use to Count how may records exist in a group.

Example. I have records labled M right, M left, N right, N left,.....U right, U left. Each letter represents a server cabinet

In a input cell, I can select M right.
In a second cell, I can select U left.

I need to have the formula calculate the number of Cabinets'
(M, N, O, P, Q, R, S, T, U) that it takes to cross the cabinets = 9

The problem I am having is that the formula has to change based on weather they select the left side or right side of the cab.

First input cell = M right
Second input cell = U right

I need to have the formula calculate the number of cabinets
(M,N,O,P,Q,R,S,T) that it takes to cross the cabinets' Note: U is not counted. = 8

View 9 Replies View Related

Search Excel For Cells With Numbers Of Certain Colour And Changing Said Colour

Feb 13, 2014

Is it possible to search Excel by cell colour, i.e. font colour...and then change the said colour to another one?

View 3 Replies View Related

Conditionally Count Number Of Entries Per Month

Sep 15, 2009

I would like to count number of approved projects (Yes) and denied projects (No) on monthy basis. See attached file for more info..

If F17 is "No" on the month of Sept 09, then cell "O2" should count number of "No" in the month of sept.

View 8 Replies View Related

Changeing A Cells Colour Basing On Other Cells Colour

Mar 23, 2009

i want a cell to change to the colour green if 5 other cells on the page are green colour.

I want a cell to change colour to red if there are 1 or more red cells on the page.

How do I make this work?

View 8 Replies View Related

Colour Cell Based On Few Conditions / Cell Values Of Other Cells In Same Row?

Jun 28, 2014

In the xls for each step I have 2 raws-planned and actual. Step planned duration is populated manually over the weeks.Before that row we have another reflecting the actual step status per week

I would like to find a way how cell reflecting the actual status of a step can be automatically populated (coulored) based on the colour/value of activities that are planned for that week and for that step.Activities are listed below the step and again have planned and actual row.

The rule should be : if for a week we have several activities all of them should be finished in order to have step stataus auto populated as green. If a single activity planned for that week is not done-then weekly step status should be red.

The activities for each step are grouped below the step. It seems that one of the difficult part in that request would be how formula will understand where starts and finishes the activities that belong to one step. To get that happen I placed a column showing step and another column where we activity.

View 9 Replies View Related

Colour Data Points On Chart From Dynamic Cell Colour?

Oct 3, 2012

I would like it to loop through all the charts on the "Graphs" sheet.

It works well but gives a "Run-time error '1004'" Application-defined or object-defined error afterward that I would like to get rid off.

Sub Colour_Chart_working()
Dim rngColors As Range
Dim rngCell As Range


View 1 Replies View Related

How To Change A Cell Colour Depending An A Different Cells Value

Oct 22, 2009

How to a change a cell colour to say red in B6 if cell b12 = 1 and if e6 = 1 to change to green. I thought I might be able to use conditional formatiing but no. I had set the spread sheet to do a cell just for a condition representing a sum from another worksheet and it was working fine but i have to incorporate the two together and am stock.

I have attached a spread sheet.

View 14 Replies View Related

Copyrights 2005-15, All rights reserved