I once read (it may have been here but I can't find it with a Search) that you could use the colours of the cell as the determination whether to include the value in a calculation. i..e if the cell is red, include the value in the total.

Anyone seen this or know how to use it?

Thanks

Anyone seen this or know how to use it?

Thanks

- Sum by cell color in Excel 2003
- Interrogating cells by cell formatting
- Event triggered by cell format change?
- Counting by conditionally formatted cells
- Absolute Sum of cells by colour
- Sum of cells in a column of rows returned by autofilter
- Sort by cell format
- Subtotal By Cell Color
- Sum & Count Cells By Fill Color
- Counting Colors of Cells set by Conditional Formatting
- 2 Excel Functions/Formulas to Count/Sum Excel Cells by Color - Excel ...
- Format cells-how can i set by default the cell formatting
- Sum by cell color:conditional formating rule
- Searching by cell format colour, then copying and pasting?
- How to sum up cells and multiply by 10, min 10 max 100?
- Sum of cells colored using conditional format
- Forcing cell format
- Sum Different Cells From Different Worksheets
- Force cell format as text
- Toolbar Button Images, Word Wrap, and Cell Format
- Summary Page in workbook, summing concantenated cells returns a 0 value.
- Copy And Paste Row By Cell Value Criteria
- Function to sum same cell in multiple files
- Cell Formating Slows Down AutoFilter

Is there anyway I can interrogate cells by what formatting they have?

For example, can I use an IF statement that tells excel to return the value

of a cell if it has a bold format, or otherwise return nothing?

I have a huge table of data that I have imported from word, that I need to

manipulate.

Any help would be much appreciated.

Regards,

Pinda.

I'd like to run a macro (similarly to a Change event) when the cell format

(e.g. font color) of a cell changes.

How can I do that?

Thanks,

Stefi

I'm using this UDF to count cells based on fill color http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm

This seems a well respected method to do so, however, i can only get it to count cells of which i have manually changed the fill color.

Some of my cell colours are changed by Conditional Formatting. I cannot get this UDF to count these cells?

Can anyone help me please?

G

I am using the following formula: http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm

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

=SUM(ABS(M32)+ABS(P32)+ABS(AK32)+ABS(AL32)+ABS(AM32)+ABS(AP32)+ABS(AS32)+ABS(AU32)+ABS(BB32)+ABS(BE32)+ABS(BR32)+ABS(CC32)+ABS(CD32)+ABS(CI32)+ABS(DE32)+ABS(DJ32)+ABS(DL32))

Would be the sum if i manually entered the name of each coloured cell which is the same as M32?

Is there a way to edit the VB code to make it add up the cells by ABSOLUTE VALUE?

-Craig

The example is a bit contrived, but it illustrates what I am trying to do: make a summation cell show only the sum of cells in a column for rows returned by an AutoFilter selection, independent of what AutoFilter specification is selected.

Row 1 Contains summation cells

Row 2 Contains column headers

Rows 3 to N contain data

Col A: Name of Metro Area

Col B: Population of Metro Area

Col C: Contains an "x" if Metro Area has a median income over $35,000

Col C: Contains a "y" if Metro Area Has a median income over $50,000

Col D: Contains an "x" if the Metro area is northern

Col D: Contains an "y" if the Metro Area is southern

Cell B1 = sum(B3.B65536)

Col A Col B Col C Col D

Row 3: Detroit 4,000,000 (null) x

Row 4: Atlanta 3,000,000 x y

Row 5: Dayton 300,000 y x

Row 6: Macon 100,000 x y

With AutoFilter on and:

Nothing filtered, I want cell B1 to show 7,400,000

Col C filtered for I want cell B1 to show

x 3,100,000

y 300,000

(Blanks) 3,000,000

(NonBlanks) 3,400,000

Col D filtered for I want cell B1 to show

x 4,300,000

y 3,000,000

(Blanks) 0

(NonBlanks) 7,400,000

Can this be done?

If so, can it be done within Excel, or does it require VBA?

Either way, how might it be done?

TIA,

Far Farley

I want to sort all rows by the strikethrough formatting so these rows will be at the top of the worksheet.

Any help on this would be most greatly appreciated.

VB:Cell formula =colorfunction($H$10,$A:$A,FALSE) - counts coloursFunction ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

=subtotal(3,B12:B6000)

I've made a quite simple work-calendar & now I have made some buttons for each type of holiday. Depending on wich button you click, the selected active cells will be coloured in the respective colour.

Now, that works & it's not that hard.

But the real problem I have is one I can't seem to fix.

If I make a User Defined Function to sum & Count like the one explained here (http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm) I get an error each time I try to insert the formula (=ColorFunction($C$1,$A$1:$A$12,FALSE) into the formula bar.

The error message tells me that the formula has an error in it and it asks me if I tried to put in a formula or not. If not I have to leave the '=' away.

Now, IT IS a formula so I don't know what I'm doing wrong...

I've uploaded the excel file to my own server because it exceeded the filesize overhere.

This is the link --> http://www.dj-jug.be/calendar-example.xls

I Hope someone can help me.

Greetzzz

(http://www.cpearson.com/excel/colors.aspx)

It works fine on the background color but doesn't seem to pick up any

subsequent changes to the background color made by conditional formatting.

Does anyone know of a way I can count colors of cells set by conditional

formatting

Custom Excel Formula to count and or Sum by color ... Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00 . $59.95 Instant Buy/Download ...

i mean i dont want excel to correct let's say: 1.5 to 01.may or 9/12 to 09.dec. is this possible?

thanks.

(i have some programs which export data to excel and instead of numbers in excel appear dates and i can't set the cell format before the data is exported)

Any help would be apreciated.

Thx in advanced.

Dragos

I'm at a complete loss when it comes to VBA, however I have a specific task I need to perform on a spreadsheet for work which will require the use of VBA.

We have a spreadsheet which is used to log all service calls from our customers. This spreadsheet has a multitude of columns and thousands of rows (one row per service call) one cell in these rows contains has the problem entered into which requires the service call. There is one type of problem which we are tracking as it seems to be a common occurance. This type of problem has recently started being tracked by formatting the cell background to blue.

Any of the service call rows which have a blue background in the problem cell are currently being manualy copied to a separate workbook. This isn't a particularly slow operation, however it is annoying and a duplication of work which I'd rather my staff do not have to contend with. So here's my query that hopefully you can help me with.

I need to write a piece of VBA code in the separate workbook which will check the service log for these blue background cells and then copy the relevant information from that row to the separate workbook. Ideally this code would run on opening the workbook, but I am quite happy to attach it to a button if it would make the coding easier.

Hope I have explained it clearly enough, thanks in advance for any help!

Martin

I'll try to explain this as best as I can. I'm trying to figure out a formula that will sum up several cells and multiply that by 10, however the minimum figure must be 10 and the maximum 100.

For example:

A B C D E F

50 1 1 1 1 1

If I have five cells with one it would calculate to 50 in cell A.

However, if the sum times 10 is say 5 I need it to be 10, and if the sum times 10 is 200 I need it to be a maximum of 100.

Something like sum of cells multiplied by 10, if less than 10 than put 10, if more than 100 than put 100.

Hope I explained it clearly enough, thanks!

I would appreciate a bit of help with my below problem.

I have a table in which I have conditionally formatted certain cells.

Now I want to sum the conditionally formatted cells (that are colored distinctly) both row-wise and column-wise i.e. the sum of colored cells.

I tried applying a vba program for adding the sum of colored cells but it would not work on the conditionally formatted cells.

Could you please guide?

Thanks in advance.

i have problem in excel that when copying from sheet to another sheet in the same workbook it takes the format of the cell as time x.xx and applies there, regardless to the cell format of where it it goes. now if applied time-difference formula on sheet 2 it doesn't work! when entering time in sheet 2 it automatically make it as HH:MM:SS AM no matter however tried to change the cell format to time: hh:mm or to customer as h:mm@ it doesn't calculate the time different between 2 cells, one is copy-past from the previous sheet, and the new one which is inserted manually as time but never is taken as the one previously copied... therefore the output sum cell gives "VAlue" even obviously there is 2 cells with "time" but never calculating,

i think it is format of cell and we have to deal with it ? can we we force the cell format in somekind of code or making the copy-paste times from previous sheet comes without the cell format?

obviously this is to be deal with low experience IT stuff employee so should be protected not to change the cell format manually each time, they have tonly to insert the manul part then after that, the formula should caluclate this in protected cell

what you think guys? possible?

Mike

SUM(Sheet1!A1:A5, Sheet2!B2:B3, Sheet3!A20:D20)

but how can I do that by simply highlighting the desired cells and using the summation button rather than manually typing in the formula?

Thanks very much for your help!

One is the Define Name command for working with named ranges and constants. Another is Conditional Formatting.

Anyway, I created a page on my site for listing a few button images that you can download, along with how to add them to existing command buttons. If anyone has any really cool button images, I'd be more than happy to list them (and include your name if you want).

Excel Toolbar Button Images

By the way, I finally figured out how to preserve the transparency in GIF images. The trick is that you have to insert the picture into Excel first. Then copy it. Then paste the image. I listed the steps on the page so that I don't forget later.

I got a little carried away, and added a couple of other macros, such as a button (and macro) for Word Wrap and a Cell Format UserForm. Instead of creating an add-in for all this, I wrote up instructions for creating your own add-in. It's all pretty basic stuff.

If you like to add labels with a lot of subscripts, superscripts, symbol fonts, etc. like I do, then you might find the UserForm pretty fun. It was a long time ago that I created it (back when I was first learning how to create UserForms), so I'm not very sure where the original idea for such a form came from. If anyone knows, I'd like to provide a reference.

Enjoy,

Jon

Let me begin by apologizing if I am asking a question that has been answered already. I have searched several different ways and have been unsuccessful.

I am working on a workbook that has multiple pages of data. Not too huge. I have totals on each page, some that are dependency based. On my summary page I am using concantenation operators to fill in some totals. When I try to sum the cells (s24;s25 on SF6 Usage Report) with those operators, I get 0.

Is there a way to make the sum function compute after the cells complete their respective formulas? (if that is the problem)

I realize that this is probably the ugliest workbook you have ever seen, and I apologize. Until I agreed to take on this project, I thought I knew alot about Excel...I have since realized that I am probably not even a novice.

I have attached the workbook for you to check out. If you have any suggestion to make this book cleaner, I would greatly appreciate it.

Thank you in advance for any help you can offer.

Shawn

p.s. I am not very familiar with vba's and pivot tables, so that may be why I haven't understood that I have found an answer already.

the code below is working fine

VB:CommandButton1_Click() Dim sfind As String Dim cl As Range 'check for a value & show message Sheet = "Data" Sheets(Sheet).Select Application.ScreenUpdating = False If Me.userIDCombo.Value = "" Then 'the textbox in userform Me.userIDCombo.SetFocus MsgBox "Please enter a Name to Update." ' show message if no Name Selected Exit Sub Else sfind = Me.userIDCombo.Value With Range("A1", Range("a65536").End(xlUp)) Set cl = .Find(sfind, LookIn:=xlValues) If Not cl Is Nothing Then cl.EntireRow.Copy ' can I copy only the Range (A:G) as per comboBox Sheets("index").Select Application.ScreenUpdating = False Range("A17").Select ActiveSheet.Paste Application.CutCopyMode = False Unload Me 'hides the userform Sheets("index").Select Else MsgBox sfind & " does not exist" End If End With End If Me.userIDCombo.Value = "" '< clear box for next use End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

The code below is not working ..

VB:saveButton_Click() Dim sfind As String Dim cl As Range 'check for a value & show message 'Sheet = "Data" Sheet = "index" Sheets(Sheet).Select Range("A17:G17").Copy 'Application.ScreenUpdating = False Sheets("Data").Select sfind = Worksheets("index").Cells(17, 1).Value With Range("A1", Range("a65536").End(xlUp)) Set cl = .Find(sfind, LookIn:=xlValues) If Not cl Is Nothing Then cl.EntireRow.Select 'Selection.Paste ActiveSheet.Paste ' Application.CutCopyMode = False ' Unload Me ' ' Sheets("index").Select Else MsgBox sfind & " does not exist" End If End With End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:This function pretends to sum all the cells (With the same cell position) in almost ten files. The files are stored on a range in the same worksheet, one before another.Range) ' Declare Functions Dim rCell As Range Dim rngFileName As Range Dim vResult 'Load names in 'Data!A1:A10' Set rngFileName = Range("A1", Range("A65536").End(xlUp)) 'Loop all the filenames and sum each cell value For Each rCell In rngFileName vResult = WorksheetFunction.Sum( '[' + rngFileName + ']Total!' + rCell) +vResult Next rCell SumInd = vResult End FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I think that the approach is good, but I'm not sure how to tell the sum function to get filenames, this does not work: WorksheetFunction.Sum('[' + rngFileName + ']Total!' + rCell)

Any help will be appreciated.

protection status, wrap, etc....

There are an additional 7 columns of formulas to the left of the filtered range.

The strange thing is----

-WITH the formating, trying to Unfilter the list takes 2 min, via a manually activated Data>Filter>ShowAll OR via a macro run of 'ActiveSheet.ShowAllData' .

(In an attempt to optimize speed, the VBA macro sets calculation to manual before the 'ActiveSheet.ShowAllData' and screen updating set to false.)

-WITHOUT the cell formating (eg. by doing Edit>Clear>Formats), the ShowAll takes about 3 sec.

Does anyone have experience or an explanation for this?

Why should the Formating affect Filtering so much?

Options for improving speed of autofilter?

NOTE:

I don't know if, or why it would be a factor, but note that I am using Dynamic Named Range and VBA to expand/contract the formulas

and formating to size of the list/table. Although this is not done during the filtering use.

Here is the dynamic formating code

VB:I am wondering if some strange 'artifact' of manipulating the formating is becoming a factor ??DynFmt_List() Application.ScreenUpdating = False With Application .Calculation = xlManual End With '///DEFINE DYNAMIC Format List RANGE '/// dynamic range adapts to width and length of list, +501 rows ActiveWorkbook.Names.Add Name:="zdynFmtList", RefersTo:= _ "=OFFSET(ObjectList!$I$13,0,0,501+MATCH(""*"",ObjectList!$M$13:$M$20000,-1),COLUMNS(ObjectList!$I$13:$AB$13))" '/// Clear the formats for whole list Range("I15:AB20000").Select Selection.ClearFormats '/// Copy the 'seed' format from the 'top' two rows of list Range("I13:AB14").Select Selection.Copy Application.Goto Reference:="zdynFmtList" Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Application.ScreenUpdating = True End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks in advance for your responses.

Dave