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


Count Cells Based On Background Color

I need to be able to count the # of green cells in a range independant of what is in the cell. I can't to do it in VBA.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Count By Font Color & Background Color
In excel 2000, I am trying to count the lines in a spreadsheet that has the first cell in each row highlighed in yellow. Additionally, I am trying to count the number of rows that has a red font used in the cell in column D.

I have reviewed other posts by searching counting highlighted cells, but I do not understand how to complete the entire process. One of the suggestion was to create a vb script in combination with using =cellindex(??). I do not really understand how to create this, name it and the run it.

View Replies!   View Related
Count Cells Based On Font Color
Hi, I've been cracking my head for a few days your assistance is much appreciated.


The ones in red is conditionally formatted. So I want at the end of each row to count how many are in red.

View Replies!   View Related
Filter Rows Based On Background Color
how to filter rows based on the background color used in a cell? I need to make a macro that will filter/hide all rows where the cell in column B has the background color set to Yellow. Is there a way to test for that so I could get a true/false value in a column to filter by?

View Replies!   View Related
Conditional Format Based On Background Color
I'm attempting to write a conditional format formula that will change the background color based on multiple color options on in a row. I have cell A:c as first, last and middle name. I utilize the rest 40+ cells that provides dates that are conditionally formatted to based on dates. I've approached this trying to reference the columns dates and cannot figure it out. Heres what I would like to do:
If any cell in from D:Z has a background color of red, yellow, green, and none it will change the background color in cells A:C. Its easy to reference one cell but not multiple. I've attached a sample of what I would like to do and I manually changed to cells A:C to show how I would like to show my results.

View Replies!   View Related
How To Color The Background Cells
How to color the background cells using text ...

View Replies!   View Related
Change Cell Background Color Based On Data?
I want to change the color of a cell's background based on valid data in the cell. I have a pull-down list, it uses a named list, and this all works fine. I want the cell to be red, and then after the user selects a valid entry from the pull-down list, I want to change the cell to yellow.

View Replies!   View Related
Sorting Recording Based On Background Or Font Color?
Is it possible to sort the excel records based on background or font color?

View Replies!   View Related
Change Cell Background Color Based On Multiple Conditions
My Excel skills are basic at best, with no knowledge of VBA other than finding the VB Editor. What I'm trying to do: Attached is the file I'm working with, to give you a better idea. When a name is selected in column B, the cells in the non corresponding columns E to N should have a black background color. For example, when you select Mike in column B, the cells in that row in columns I to N, named Dave and Dean, should become black. If you select the name Dave, columns E to H (Mike) and M to N (Dean) should become black. In addition, the background color of the non-black cells should change depending on the status.

OK : green
FAIL: red
N/A: orange
Exceptions: blue

The attached file shows a few possible results, manually entered.

View Replies!   View Related
Macro For Changing Background Color Of The Cells
I have an excel sheet in which many cells have a background color of Green. These cells are located all over the sheet. I want to change the background color from Green to Red not affecting the color of other cells in the sheet.

I will be realy really thankful, as I have to do this exercise for more than 50 worksheets.......If I start doing it manually by selecting each celll with green color background and changing it to can well imagine my life would get shorten significantly while doing this.

View Replies!   View Related
Copy Cells By Chosen Background Color
I am using the following code to copy all rows that have a cell with a background color to another worksheet. In my first workbook this works just fine. In my new workbook, however; it is not working correctly. There are 111 rows that have a cell with a background color (all the same color). When I run the macro it is only copying the last row that has a colored cell. I have been trying to figure out the reason for the last 3 hours and I give up.

Sub CopyColor2()
Dim rReply As Range, rCell As Range
Dim lCol As Long

Set rReply = Application.InputBox _
(Prompt:="Selct a single cell that has the background color you wish to copy", Type:=8)

View Replies!   View Related
Change Background Color When Exceeding A Certain Number In Cells?
I got an excel sheet were in column A i have listed a few names and in column B I got their respective results (numbers) with a background color.

I want to insert a function in such a way that when their numbers exceed a certain number the background color changes from for example blue to green.
so >1200 = blue background color
1200-1350 = red
< 1350 = green

View Replies!   View Related
Making A Cell Change Background Color Based On If Positive Or Negative Sum Total
I have excel sheet with colored cells.

Positive cash flow cells all have green background,
Negative cash flow cells, all have red background.

I calculate all the "sub- total" columns into one "total sum" cell. Based on this "total sum" I want the cell background to be red if the total sum is negative, or green if total sum is positive. How do I make the cell change to the proper background color based on whether it's a negative or postive total sum?

View Replies!   View Related
Alternate The Background Color (fill Color) Of Rows In A Spreadsheet
how I can alternate the background color (fill color) of rows in a spreadsheet. Say I wanted every other row to be gray starting at row 10.

View Replies!   View Related
Color Column Charts Based On Font Color Data Cells
I have attached the relevant spreadsheet for which I need to alter the color of the columns based on Site number ( Sheet 1). % Mortality will be represented in the Y-Axis, and the Site numbers would be on the X-Axis. All columns (% Mortality) except one will be of the same color, and the one of a different color will indicate a specific site. As an example, site 86 is colored differently. The way I require the chart to look is shown on Sheet 1.

After reading through some great posts on Ozgrid, I managed to do this using conditional formatting (Sheet 2), but that sort of falls short because I am required to add a data table to the chart, and the parameter that is indicated by the column bars happens to appear twice in the data table.

I was wondering if this can be automated maybe using VBA, but with the possibility of simply matching the color of columns with the font color of respective entry in the data series.

View Replies!   View Related
Macro To Color Cells Based On Their Font Color
I have a spreadsheet that i download from the net daily, which is seperated into columns of information.

I want to be able to look down a column and mark a cell in a seperate column if the cell font text is red.

For example looking down column A ... if the font text of a1 is red then mark the cell background colour of T1 red - if a2 text colour is red then mark the cell T2 red .... etc etc.

If the font colour in a1 or a2 ... etc etc is any other colour then do nothing.

I have 5 columns I wish to look down and mark in 5 seperate columns - I have tried to do this by conditional formating but don't know the fomula for checking font colour.

View Replies!   View Related
Color Cells Based On Color Of Preceeding Cell
I am looking for a very simple script that will achieve the following:

On the clicking of a button, Select and shade in a cell yellow, delete the yellow shading of the previous cell. The shading & selection should move up a column of cells, 1 at a time, in the following order:

From B10 to B9, then B9 to B8, B8 to B7 etc until the selection and shading is at B2. Once it is at B2 subsequent clicks will simply keep it at B2 (the top). Thus after 8 clicks the shading & selection should travel from B10 to B2, with only 1 cell being shaded yellow and selected at any one time.

View Replies!   View Related
Count Sheet Tabs Based On Color
I have a worksheet with like lots of color coded tabs and the tabs increase everyday. The tabs are categorized based on 6 colors. I created a first tab in order to give how many tabs i have another each color. Is that doable in excel. Do I need a macro for this.

All I want is each time i open the excel sheet the first tab will be automatically updated with how many tabs I have under each color instead of me counting the tabs.

View Replies!   View Related
Count Cells By Color
I am looking to count the number of cells colored RED(Fill Color) in a row. How do I go about it? Please give me detailed steps and the Code in VBA.

Lets say I want the count the number of red cells from A10:F10 and get the result in G10.

View Replies!   View Related
Count The Cells By Color
I would like to count cells by bgcolor if it is red.

I have already posted a thread earlier on this issue. But I could not find it in the threads.

View Replies!   View Related
Sort By Row Background Color Possible
I have a spreadsheet that has differen color rows for certain data. Is it possible to sort a spreadsheet by row color instead of row header?

View Replies!   View Related
Textbox Background Color ..
How can I add some code to the routine below in order to change the background color into green?

View Replies!   View Related
Count Number Of Cells That Have A Certain Color
I would like to count a column of cells and return the total number of cells that are colored yellow, for example......

Is there a way to do this in Excel/VBA ?

View Replies!   View Related
Count Cells By Their Fill Color
how can i count the number of cells by their background/fill color?

Can I use the function COUNTA??

C3A4B5C6D7E8F9G10 11COUNTA(C3:C10)

View Replies!   View Related
Count & Sum Cells By Font Color
I have a rows of cells,each with over 30 columns.The writing is in 3 fonts,blue,black and red.

I want to award a score of 1 for a blue font,0 for afont cell and -1 for a red font and then get the total score for each row,to place in an additional column on the right.

View Replies!   View Related
Count Cells Not A Specific Color
I need to use the COUNTBLANK formula but don't want to include cells that are in grey.

View Replies!   View Related
Setting Background Fill Color In VBA
I'm admittedly a novice in VBA but I'm trying to simply settnig the background color of the active cell. I've seen various snippets on the web that reference this via setting the color.index variable, using the RGB codes, etc. but nothing seems to work. I'm sure I'm missing something obvious. I know from tracing thru that the assignment instruction is being executed. I've tried using Selection.blah instead of ActiveCell, etc. also.

Select Case Total_Points
Case Is >= 70
ActiveCell.Interior.ColorIndex = vbGreen 'Green
ActiveCell.Interior.Pattern = 1 'xlSolid
ActiveCell.Interior.PatternColorIndex = -4105 'xlAutomatic
Client_Type = "Elite Partner (" & Round(Total_Points) & ")"

View Replies!   View Related
If Statement Dealing With Color Background
I am trying to write an If statement that would search a column for cells that have red as a background color, and if they did, would mark the cell with an X.

View Replies!   View Related
Return Background Color Of Cell
On one worksheet I have a list of colours in cells. I want to be able to look in the list the list and return the background colour of that cell. The function i am using is:


'1.1' is the worksheet name, $C28 is the cell (coloured blue)

It seems to work because I am getting the correct background colour in the new worksheet, however it also returns the number(37) as well.

I just want the background colour to be shown only, not the color's number as well.

Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1, 1).Font.ColorIndex
CellColorIndex = InRange(1, 1).Interior.ColorIndex
End If
End Function

View Replies!   View Related
Default Background Color Is Purple?
When I open a new worksheet it is purple. The fill color bucket it defaulted to no fill. I select the entire worksheet and click on the 'no fill' and get a normal white background. If I delete anything on the spreadsheet, where the items were deleted the background is again purple. I have to select and again click on 'no fill'. I am constantly fighting this creaping purple. Just started about a week ago.

View Replies!   View Related
Forms Button Background Color
Can the backround color on a forms toolbar button be changed or must I use a control toolbar button if I want the button in color? I would prefer using the forms button and have tried customizing (tools/customize/forms) then right clicking on the forms button icon and selecting Edit button image, clicked on the desired color and clicked ok.

View Replies!   View Related
Background Color In Column Chart
I have a column chart that goes 0-100%. The back wall is red from 0-60%, yellow from 60-80%, and green from 80-100%.

To do this, I shaded some blank cells to the same proportional shape, did a screen capture, saved that image as a file, then imported it back in as the background.

Problem is, those color breakpoints (60%,80%) may change, and that process is a pain. Is there an easier way to do this such that if I change the values the background will change automatically based on the shading of some cells?

View Replies!   View Related
Clear Background Color Before Print
I have a excel file wherein background color as well as conditional formatting background color is used. However, since printout consumes huge ink for those sheets, i want that before print the code will clear all the background color (done through Fill color & conditional formatting color) and after the print activity, restore those color. the colors are given to faciliate data entry and better presentation.i have recorded the same but background is not restored when I run the code And also sometimes the code doesnot start printing.

View Replies!   View Related
Background Color And Border Around Macro
I am trying to develop an Excel spread- sheet by using some macros. In detail, in my first worksheet, I have something like -

Exp1 ....
Exp2 ....
Exp3 ....
Expn ....

This rows are coming from another worksheet and I have used a macro (developed by me) to populate these rows. Even the number of rows to be populated is not fixed.
However, I would like to add one row with text - " Total Expense" after those rows. This row will have some background color - say Tan - and font color - say Green - and also a border around its own - in nature, the border will be thick, Double and with color Rose. I have tried the following code in macro but not working properly -

Sub Include_Fields_n_Format(loc As String)
tot_exp_scell = loc & f_scell
tot_exp_ecell = loc & (f_scell + 1)
Range(tot_exp_scell, tot_exp_ecell). Merge
Range(tot_exp_scell, tot_exp_ecell).WrapText = True

Range(tot_exp_scell, tot_exp_ecell).Interior.Color = RGB(150, 150, 150)
With Range(tot_exp_scell, tot_exp_ecell).Font...................

View Replies!   View Related
Count If The Interior Color Of Cells Is Blue
Can I have a formula which counts blue colored cells in column A.

View Replies!   View Related
Count Cells With A Specific Fill Color
If I have cells in my worksheet filled with yellow color, is it possible to count them based only on this criterion (fill color). They might or might not be in the same column.

View Replies!   View Related
Conditional Count On Specific Color Cells
I need to count the grey coloured cells in a column based on the value in a different column (Column I has the team that the person is in). I already have a code from a previous thread to count the coloured cells (below) but this doesn't work when using an array formula. I have also tried just selecting the cells I need it to count (the people within the specific team), but the formula doesn't work unless the range uses adjacent cells...

View Replies!   View Related
Count Cells Matching Any Color In Specified Range
Im trying count cells based on the cells color. My sheet has multiple blocks filled with different colors. I have searched all of the regular sites out there for vb code related functions, but I can find one that will let me select a range of colors and a range of cells to be counted.

View Replies!   View Related
Msgbox If Background Color Exists In Range
If there are any cells highlighted in red (using the conditional formatting in excel 2003), I want a message box to pop up when they go to save saying something along the lines of, "Hey buddy, you really need to deal with this."

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
Modify Code To Change Cell Background Color
I have the code below set to change the background color based on certain conditions. What I need to do is change it so that if none of the conditions are met, it does not change the color at all. As it is written now, if anything is entered in a cell that has a background already set, but does not meet these conditions, the cell turns white.

View Replies!   View Related
Count Conditional Format Color Cells- Used Gantt Code
I've created a Gantt chart using the tip #58. Now how can I count all the cells that are colored?

View Replies!   View Related
Color Cells Based On Adjacent Cells Being Empty & Add Text
Im trying to make my life a bit easier, by adding a few macros and formulas to the spreadsheet (Everything was done completely manually before I got here!!!).
What I would like to do is take two columns, which contain a start and end time for work shifts, and colour them GREEN once I have entered a name in the Worker column (Along side the two with the time), and also to fill a cell with a Yes or a No. Im aware of auto conditioning, and Ive tried to have a play to get this to work, but I just cant work it out.
I have posted a link to an image which shows what I want. I hope I've explained it well enough!

View Replies!   View Related
Macro Button To Change Background Color Of Specific Cell
My boss wants a spreadsheet that has multiple buttons on it that will change the background color of a specific cell to four different colors. Example:

Text written in Cell B3, Button in Cell A3 that will change the Background color of Cell B3 to either Green, Yellow, Red or Blue.

There will be a lot of buttons on this worksheet following the same format as above. I don't want to change the value of what is in the cell, just the background color.

View Replies!   View Related
Fill Cells Color In Worbook Based On Other Cells
how can I automatically fill cells with certain colors based on the value of the cell. (i.e. I want to search an entire workbook and fill cells with values between 80 and 99 green, 60 to 79 yellow and 0 to 59 red.)

View Replies!   View Related
Color Cells Based On Multiple Conditions Of Different Cells
Im trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. Ive searched and found similar forums here, but since Im ignorant in VBA code, I couldnt manage to make adjustments. So if you can help me with the code, I would really appreciate it!

Condition and Results required would be:

IF column C = S than on Column B = color cell light blue with white border
IF column C = P than on Column B = color cell Green with white border
IF column C = A than on Column B = color cell Yellow with white border
IF column C = L than on Column B = color cell Red with white border
IF column C = C than on Column B = color cell Dark Blue with white border
IF column C = than on Column B = color cell white
IF column C = V and has white background than on Column B = color cell white
IF column C = V and has a Dark Grey background than on Column B = color cell Dark Grey

I dont know if the last two are feasible.

View Replies!   View Related
Color Cells Based On Others
I have a long standing problem, i need to use some code that will search a range of cells , say j1 to j15, for a text "ship", gather the value from the equivalent h column cell and then use that value in futher code to colour some cells. So if the value returned is say 5, then colour the next 5 columns from column A whenever "ship" is typed.

View Replies!   View Related
Color Cells Based On Value Above/Below
I have many cells in a column, where I would like to change the colour depending on the value in the cell above. If one cell is more than or equals 600 000 and the cell below is less than 600 000, then the lower cell should change colour to red.
This should be repeted for all cells in the column, until the last empty cell.

Sub macro()
x = 7
Do While Cells(x, 4).Value <> ""
If Cells(x, 4).Value >= 600000 _
And Cells([x + 1], 4).Value < 600000 Then _
Cells([x + 1], 4).Select _
Selection.Font.ColorIndex = 3
End If
x = x + 1
End Sub

For this code I receive the error code "Compile error: End if without block if".

View Replies!   View Related
Color Cells Based On Difference Between Two Cells
I have a 2 lists of manually entered values where the values in each row are related. They are related in the sense that if the difference between them is equal to or less than, a given amount then the cell containing one value is colored Blue. e.g.

COl A holds Value x while COl B holds value y. I need to check which value is entered in columns A and B and if a given difference or greater exists colour the cell in COl B. For example the relationship relevant to a COL A value of 21.0 is 11.0.

So, I enter the following;

A1 B1

21.0 13.0 <----------------No color Blue

I enter new values

21.0 9.5 <----------------B1 is blue.........................

View Replies!   View Related
Returning A Row Of Cells Based On A Color
I am having a bit of a problem writing a formula that works under the specific conditions that I need.

I have a range of 6 cells, A1:A6 which contain text
I have formatting that turns that row red based on specific conditions. The problem is that if A1:A6 is red, I then need the text in those cells to be copied to Sheet 2 B1:B6.

View Replies!   View Related
Copy Cells Based On Color
i have att a workbook with 2 command buttons on sheet "quote"
one turns the selected cell red.
the other one i would to have select the red cells then copy to sheet1 then change the color back to white

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