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:
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
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
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
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
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
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
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
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
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
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
Change Cells Colour: Range Error '1004'
Run-Time error '1004':
Method 'Range' of object'_worksheet' failed

The Function basically takes any cell in range with a formula and has it blue. If the formula is overwritten by the user it turns the cells colour Red.

Private Sub Worksheet_Change(ByVal Target As Range)

'change Colour of cells from blue if formula based to red if data typed by user

If Not Intersect(Target, Range("AS63,BA5:BP66,BT7:CI55,BU60:BU64,BX60:BX64,CA60:CA64,CD60:CD64,BT55:CI66,BT59:CI59,CF7:CF55,CF65:CF66,DJ19:DJ21,DJ24,DL5:DM36,DJ41,DJ45,DJ48,DL41:DM48,DH50:DH51,DJ50:DJ51,DL50:DM53,DH63,DJ63,DL55:DM58,DL60:DM66,DU5:DV33,DU37:DV58,DZ8:EB8,ED5:EE27,ED31:EE66,EM5:EN12,EM16:EN29,EM33:EN38,DH63,AL5:AM26,AL30:AM49,AL53:AM66,AV5:AW16,AV20:AW29,AV33:AW53,AV55:AW63,CO5:CO66,CQ5:CR66,CY5:CY66,DA5:DB66,DJ5:DJ7,DJ14:DJ15,DJ17")) Is Nothing Then
If Target.HasFormula Then
Target.Font.ColorIndex = 11
Target.Font.ColorIndex = 3
End If
End If
End Sub

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
Fill A Range Of Cells With A Colour And 'Merged' Text
I'm trying to create an excel spreadsheet to manage personnel and various ongoing projects, what I'm trying to do is create a custom box with a selection of options (5) that will automaticaly fill a selected range of cells with a colour and 'merged' text that was selected from the custom box.

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
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
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
VBA- Delete Entire Row Which Has The Highlighted ...
i have over around 1500 pfolios in Col A, i need macro that will delete entire row which has the highlighted pfolios. Now i tried advanced filter which would work but the date in Col A6 will change every month, so dont want to go down that road, is there alternative option other than advanced filter ..

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
Trying To Use Vba Code To Evaluate Each Row In A Highlighted Selection
I have a worksheet (with if statements) that I throw numbers from a report into and it uses those if statements to calculate the number of items and money earned. The problem is that sometimes employees use different key words for their items so the formulas dont pick them up and this skews their results for the month. Also it's hard to see where someone messed so I'm trying to write a code that reads each row for a given selection and calculates the sum and if that sum is 0 then we can look for where the employee made an error. This is what I have:

Dim RngToSum As Range
Set RngToSum = Selection.Rows
For Each Row In RngToSum
If Application.WorksheetFunction.Sum(RngToSum) = "0" Then
MsgBox "Lets play find the error"
Selection.Rows.Interior.ColorIndex = 3 'red
Else Application.WorksheetFunction.Sum(RngToSum) 0 Then
MsgBox "Congrats there's no errors"
End If
End Sub

The problem with the code is that I want it to read every line and if there's no errors then have it say "congrats no errors" after ALL rows have been checked because right now it pulls a msgbox per row and I don't want to go through hundreds of no error msgboxes. However, if even just one line has an error then have a msgbox come up.

Also there's something weird where this code wont pick up an error for a row if i highlight two rows (one row without an error and one with) but if i highlight just rows of zeros then it'll pick it up.

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
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
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
[User Forms] Check Box Is Highlighted?
My problem is trivial but annoying. When I initiate my user form, one of the check boxes has a dashed border around the outside as if it was selected. No matter what I try, I cannot get the form to initialize without effecting this box. If I delete the box and recreate it, another box simply inherits this problem.

I have attached the spreadsheet, please Click the button over "A1" to see what I mean if you have the time.

View Replies!   View Related
Summarize The Red Highlighted Parts Of The Code
I have the following code used to fill a listbox control .. and I added some conditions to the code in order to give special authorities to specific users depending on their position and unit? Authority, UserPositon and UserUnit are functions give the user's position, unit and his authority. Can we summarize the red highlighted parts of the code because I am going to use these parts in many other forms of my project?

View Replies!   View Related
Highlight Every Other Row & Stay Highlighted After Sort.
I have an excel spreadsheet that shows every other row in a pale blue. When I do data sorts, the rows loose their order. Is there a way to keep the row color in every other row and still keep it that way after the data is sorted?

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