Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Sum If Cells Highlighted Particular Colour

I know I've written a formula like this ages ago, but I can't seem to recall.
I want to write a formula that says to look at a table of cells, and to sum all cells that are highlighted a certain colour.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Absolute Sum Of Cells By Colour
I am using the following formula: [url]

However I need it to add all cells by ABSOLUTE value i.e....

View Replies!   View Related
Formula That Will Sum The CCY Amounts As Highlighted In Yellow
is there a formula that will sum the CCY amounts as highlighted in yellow?


View Replies!   View Related
Sum The Sales Revenue Column Of The Attached Per Month Into The Yellow Highlighted Cell @ The Bottom
is there a formula i can use to sum the sales revinue column of the attached per month into the yellow highlighted cell @ the bottom?

View Replies!   View Related
Changeing A Cells Colour Basing On Other Cells Colour
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 Replies!   View Related
Count Highlighted Cells
I have a column with data that through code certain cells are highlighted. I want to count the number of cells that are a certain color, such as how many are red,how many are yellow,etc..

View Replies!   View Related
Counting Blocks Of Highlighted Cells
I have huge sheet with time periods across the top and accounts listed down. I have manually shaded cells red that represent "new" sales. I have used the custom function "colorfunction" to count these red shaded cells in the past. Now, I am trying to count blocks of cells. Each block is a different # of cells and represents sales $ over a different period of months. I want excel to count each block (start to end) ans add them up... I can SEND THE SHEET IF YOU WISH....

J F M Ap M....
acct 1

acct 2

acct 3

View Replies!   View Related
If Statement Searching For Highlighted Cells
Would like to write an IF statement where it reads if any cells in a column are highlighted and gives the sum of those highlighted while ignoring the ones unhighlighted.

Is there any way to do this or an alternate method that would possibly work?

View Replies!   View Related
Conditional Formatting Certain Cells When Another Cell Is Highlighted
I want to change (via conditional formatting) the background colour of cell H64 and K72 when I select / highlighted / activate (i.e. just left click in the cell) cell C66.

I don't seem to be able to find in the formulae something to indicate if C66 is selected / highlighted /activated.

View Replies!   View Related
Deficit Cells Highlighted In Red If The Deposits Are Less Than The Expenses.
****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"> Deposits
Monthly Balance
$ 2,058.20
$ 1,509.10
$ 753.86
$ 549.10
$ 2,185.05
$ 1,939.35
$ 999.56
$ 245.70
$ 1,078.90
$ 1,278.50
$ 799.96
$ (199.60)

I want the Surplus/Deficit cells Highlighted in red if the Deposits are less than the expenses.

Can I this? Maybe using a macro or formula not entirely sure
If so how?

I would like to be able to this myself but could not figure it out using conditional formatting in Office 2007.

View Replies!   View Related
Color Function To Count Cells Highlighted In Different Colors
using =ColorFunction to count cells highlighted in different colors.

Is that function available in Excel 2007 under a different function name?

View Replies!   View Related
Highlighted Cells (enter A Character And For Each Cell Within The Selection That Value Is Entered)
when i highlight any number of cells what i want to be able to do is enter a character and for each cell within the selection that value is entered.

i know this is possible by pressing CTRL + ENTER but is there a way i can code it onto the sheets module?

View Replies!   View Related
Advanced Goal Seek: Two Cells Missing Highlighted In Blue
I have the total sales for a company. Then I have four specific segments that fall under those sales. Two out of the four segments I have exact sales figures for. The other two segments I just have the year-end sales and the dollar increase in each quarter. Is there a way to solve in excel for the missing quarters. So each of the four segments would have to add up to the total sales for both the quarter and the year. I've attached my example spreadsheet too. There are two cells missing highlighted in blue but i'm going to try to obtain that data. provided i fill in those two blanks, is there way to back into the rest of this spreadsheet?

View Replies!   View Related
Sum Based On Colour
i'm currently using some VBA code to insert a formula and sum certain rows. Which rows to sum is determined by the following line...

View Replies!   View Related
Sum Values Based On Cell Colour
Can you sum cells based on colour, and if so, how do you determine what code/number your colour is to input into the programming?

View Replies!   View Related
Formula To Change Cell Colour :: Due To Sum Increase Or Decrease
how to change the cell colour due to a sum increase or decrease? For example: if the amount is greater than 200, change cell colour to green. If under 200 change cell colour to yellow.

View Replies!   View Related
Determine Values In Cells: Get The Lower Values Between To Cells And Have The Lower Valued Cell Highlighted
I M trying to get the lower values between to cells and have the lower valued cell highlighted,,,i have over 43 thousand lines of data to go throughand i was wondering if there was a quicker way to do this,,,for example cellA1 is $4.25 and cellA2 is $5.25 i want cell A1 to be highlighted,,is there a way?

View Replies!   View Related
Selecting Only Cells Of A Certain Colour (CF)
A macro that only selects cells which are coloured light green. I need this so that I can add it to an existing macro that I've written to remove conditional formatting from these cells only.

View Replies!   View Related
CountIf Cells Are A Certain Colour?
Can I use a CountIf formula to count coloured cells that have been formatted? I tried to use a CountIf formula to count the % but because some of the warning and failing % are different that would distort my results.

View Replies!   View Related
Cells Need To Change Colour
I have developed a profit loss sheet for the office I work for. What i need is to have either cells change colour or the numbers in the cell change colour depening on the value in the cell.

What I was thinking is if the number is over 15% of the sale price the cell or number should be green, if the number is under 15% it should be black, and if its below 0% it will be red.

View Replies!   View Related
Colour Coding Cells
Is it possible to select a number of cells that are say yellow when blank but once someone type "N" or "No", they change to blue and if "Y" or "Yes" they change to green. I tried conditional formatting but it does not change the blank range to yellow until I have entered something into it.

View Replies!   View Related
Delete Words With Certain Colour In Cells
May i know how to delete the words which is red color in cell. What is the code like

View Replies!   View Related
Changes The Active Cells Interior Colour
It changes the active cells interior colour.
Can the it be adjusted to change the colour of the last changed cell (or range)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strRow As String

With Target.EntireRow
strRow = .Address
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=COUNTA(" & strRow & ")>0"
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Interior.ColorIndex = 8
End With
End Sub

View Replies!   View Related
Colour Any Cells That Meet The Criteria
I want this macro todo is to start at the top of columns A to J
and work down the column and colour any cells that meet the criteria.


Do Until ActiveCell = ""

If ActiveCell > 0 Then

Selection.ActiveCell.Interior.ColorIndex = 5


ActiveCell.Offset(1, 0).Select

End If


End Sub

View Replies!   View Related
Counting Cells By Font Colour
I need to count cells in a column that display a red font as a result of conditional formatting triggered by the MAX function in adjacent rows.

I've tried numerous VBA codes suggested by your site and others but without success.

They work if I manually change the font colour but not when they're changed conditionally.

View Replies!   View Related
Conditional Formatting To Colour Cells
I'm designing a holiday chart in work, and am using conditional formatting to colour cells. The problem is I have more than 3 conditions. Consequently I need to use a macro.

View Replies!   View Related
VBA To Select Cells Of Certain Colour
I need a fast procedure to create a range variable which comprises of non-adjacent cells which have an interior color of yellow say, within one sheet.

View Replies!   View Related
Automatically Colour Code Cells
Is there a way to automatically conditionally format each column in each row, when either open, closed or cancelled is selecting from a drop down in the Status column. For example every cell is blank in colour, but if I select open in the drop down menu for row A2, the entire row will turn Red. Just wondered if there is a way instead of individually formatting every row separately. I would like to be able to copy the formula or whatever down the entire sheet.

View Replies!   View Related
Select Cells With Yellow Colour
Need faster way to select those cells (non-continous) that highlighted with yellow or read colour.

View Replies!   View Related
How To Change A Cell Colour Depending An A Different Cells Value
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 Replies!   View Related
How To Count Conditional Format Colour Cells
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 Replies!   View Related
Colour A Range Of Cells Based On Two Dates
I am trying to get spreadsheet cells to change colour relating to arrival and departure dates. I also need the cells to be assigned value so I can use them to calculate cost based on number of nights, eg arrive Sept 10 dept Sept 15 = 5 nights. I can then use this 5 to multiply out costs.

I have attached an example of what I am trying to achieve.

I have searched the forum and saw similar questions but I couldnt apply them.

View Replies!   View Related
Auto Format Fill Colour Background On Certain Cells
I have been working on a spreadsheet but I use autoformat for.

For example if the date in the cell matches today's date then it turns the text read and makes it bold.

I was wondering if there was a way to format several cells in a row if a certain criteria was met.

for example

1 Item Cost Quantity
2 Car £11,520 2
3 Bike £7,500 1
4 Tyres £50 4

Now for example if I set a condition whereas the quatity is greater than 3 then it would highlight the whole row.

So in the example above I would like to to highlight row 4 and Fill colour cells A4,B4 & C4 with a yellow background and Red Text and make the texted bold.

View Replies!   View Related
VBA-change The Colour Of 4 Cells Depending On The One Cell
i need to change the colour of 4 cells depending on the one cell.

i have found some vba codes on the net but they are all based on numbers and i need it on text.

so if a1 ="alert" make A1 to A4 red

if a1="passed" make a1 to a4 green... and so on

i cant used conitional formatting as i need more than 3 colours

View Replies!   View Related
Colour Range Of Cells In A Row Based On Value Of One Cell
I would like to change cells A2 to G2 to a different colour based on the value of G2

(0 to 48) Dark Green
(-2) Light Green
(-4) Yellow
(-6 to -48) Red

The range of the sheet would be A2 - G130. Each Row would change on value of cells in Column G.

View Replies!   View Related
Automatically Populating A Row Of Cells With Background Colour
I have five possible reps names in column A, when i type in "peter" i want that row up to column I to have a background colour of yellow, each rep must have his own colour. when i clear the name from column A the row should clear the colour. using conditional formating i can only get the first cell to get a background colour.

View Replies!   View Related
Adding Cells Based On Shade / Text Colour
I have a spreadsheet where I am adding up figures in columns.

I need to differentiate between 4 different "types" of cells to sum.
Cells with figures in them are either green, red or have no fill; and some cells contain no figures at all.

At the bottom of the column, I need to total up all the figures in red cells, as well as all the figures in green cells.

The shading of these cells is not permanent - colours are changed as work progresses, so I need the totals to keep up with this.

If it cannot be done based on cell fill colour, is there any other way to do it, other than the usual long-winded way of @sum(..... etc

View Replies!   View Related
Finding Cells With Certain Values And Adding Fill Colour To The Cell
i have an excel file which contains data, I want to be able to search this file and to fill the cell and its next 3 cells to the right with a certain colour. Is this possible as I have wrote some vba to do this but, Im only able to search and not fill the cells with the correct colour.

View Replies!   View Related
Macro To Generate Message Box Based On Colour Of Cells On A Sheet
I have built conditional formatting into some cells on ' Sheet 1' and would like a message box (saying "Check errors before closing") to appear when the user tries to close the workbook if any of these cells are coloured red (ColourIndex 3).

View Replies!   View Related
Autofind & Sum (select The Respective Cells And SUM)
To fill in the ??? in attached file, I select the respective cells and SUM. Would there be a way to automate as:

For each entry in column D, Excel picks out itself the names in column-A where-ever they come, picks the corresponding values from column-B, sums them and reproduces the summation in column E.

View Replies!   View Related
Add Sum To End Of Range To Sum All Cells Above
I'm getting a Type Mismatch (error 13) with the following:

Range("I1").End(xlDown).Offset(1, 0) = "= Sum(" & Range("I2", Range("I2").End(xlDown)) & ")"

Basically I want to do an autosum on the first blank cell for a list of numbers in a column

View Replies!   View Related
Sum If, But The Sum Range Is Seperate Cells ?
I want to do a simple Sum if. My range is simple and so is my criteria, but the actual range of cells to sum is not in one continus row or column. Can I do the sumrange as seperate cells?

View Replies!   View Related
Counting Highlighted Rows ...
I have a spreadsheet which I have highlighted certain rows based on a certain criteria (I have manually highlted the rows). I was wondering if there is a way to "count" the number of rows that have been highligted?

View Replies!   View Related
Copy Only Highlighted To Another Sheet
I want to copy only the highlighted cells to another worksheet

View Replies!   View Related
How Do I Make A Highlighted Cell Blink On And Off?
how do I make a highlighted cell blink on and off?

View Replies!   View Related
Copy Highlighted Rows To Another Sheet
I have a spreadsheet worksheet 1 "BOM" and created worksheet 2 "Risk List"

I have multi Rows of highlighed (ColorIndex = 44) and I would like to have a marcro that copys the highlighted rows from worksheet "BOM" to Worksheet "Risk List" starting the entery at row A3 on "Risk List" as row A1-A2 are Header.

View Replies!   View Related
Unable To Get The Highlighted Section To Work
I am editing a Macro from a guy that has left my work and I am unable to get the Highlighted Section to work. In Section 'Paste Values & Formats it appears to work fine until it get down to Selection.PasteSpecial.Paste = x1PasteValuesAndNumberFormats and it throws you out. Is this because the Macro is generating a new workbook….?

In regards to the other sections; I am unsure if it does or does not work because every time I go down to it in Debug mode it highlights it as an error…

Sub Generate_Statements()

'Declare Variables
Dim i As Long, SMax As Long, SPath As String, fso As New FileSystemObject, file As file, statement As Workbook
Dim forma As Workbook, macro As Workbook, OldVar As Long, social As Long, rng As String, rng2 As Range....................

View Replies!   View Related
Reduce The Number Of Lines That Are Highlighted
I have the following code in a User form and I was wondering if there was a way to reduce the number of lines that are highlighted, but obviously still get the required results!

Private Sub cbSearch_Click()
Dim Product As Range
Set Product = Sheets("Master").Range("A1:A300").Find(Val(tbLookFor), lookat:=xlWhole, LookIn:=xlValues)
If Product Is Nothing Then
MsgBox "Incorrect Product code, try again"
'Populate the userform with data
tbData1 = Product.Offset(0, 1) 'Down 1 row, over 1 column
tbData2 = Product.Offset(0, 2)
tbData3 = Product.Offset(0, 3)
tbData4 = Product.Offset(0, 4)
tbData5 = Product.Offset(0, 6)
tbData6 = Product.Offset(0, 7)
tbData7 = Product.Offset(0, 8)
tbData8 = Product.Offset(0, 9)
tbData9 = Product.Offset(0, 10)
tbData10 = Product.Offset(0, 11)
tbData11 = Product.Offset(0, 12).....................

View Replies!   View Related
Highlighted Range In Sheet To Be Used In Macro
I need my macro to select the range that I have highlighted in a worksheet.
For every run of the macro the range will be different.

View Replies!   View Related
Row Highlighted When A Condition Is Met
I want to highlight the row when a condition is met. As by using conditional formating only a single cell will be highlighted.

in the attached file,

I had used the condition in column H, when cell contains "BLI" the cell will be highlighted in Green, but I want to highlight the complete row with green color wherever Column H contains "BLI"

View Replies!   View Related
Comparing Two Cells Based On Text, Font, Colour, Font Size Etc
I'm looking to set up a spreadsheet whereby individuals answer questions and have to format their answer using a particular font, colour, font size and so on. The idea is that I can then compare their answer sheet to a pre completed one using an =IF function and get a total score. The only problem is =if and =exact only lookup cell text/numbers and don't look at how the text is formatted within them.

View Replies!   View Related
Copyright © 2005-08, All rights reserved