I am trying to write a macro to color cells red from a:g if g > 1. I need to do this via macro and not with conditional formatting. I've tried this macro below but it doesn't work.
Set rng2 = Range(Cells(3, 7), Cells(lastrow, 7))
For Each cc In rng2
If cc.Value > 1 Then
Range(Cells(cc, 1), Cells(cc, 7)).Interior.ColorIndex = 3
End If
Next cc
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.
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.
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.
I am using Excel 2010 and basically i am trying to fill a range of cell with a green color if any value was enter in a specific cells. Example: I would like to fill range: A10:c13 with a green color (regardless of the cells content in this range) if a value was entered in cell C10 or C11 or C12 or C13.
I've tried conditional formatting but unfortunately I'll have to apply formatting for every cell and for a range of over hundred cells is not efficient.
I’m 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. I’m aware of auto conditioning, and I’ve tried to have a play to get this to work, but I just can’t work it out.
I have posted a link to an image which shows what I want. I hope I've explained it well enough!
http://img530.imageshack.us/img530/6239/excelspfk0.jpg
I have a similar question regarding coloring of empty cells between two cells with specific numbers as posted in below thread : [URL] .....
This is exactly what I want. But here I have some problem with formula.
=COUNTIF(A1:Z1,1)=1
Above formula could pick cell value 1 and color the empty cells in a row between the cells having value=1. But if I want to use the same formatting for other numbers like 2 or 3 it doesn't work.
For example in row(A1:Z1) I have A1=1, D1=1, and F1=2, K1=2, and O1=3, U1=3
I required multiple conditional formatting that could color the cells based on values first from A to D then from F to K and finally from O to U.
I'm using above formatting it works with cells having values 1 but its not working for 2, 3 or any other cells values.
I have already tried replacing 1 in above formula with 2 and 3 but it doesn't work.
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 9 Replies View RelatedI’m 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. I’ve searched and found similar forums here, but since I’m ignorant in VBA code, I couldn’t 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 don’t know if the last two are feasible.
creating this code or fomatting in my excel file. I want to fill color in cells between the two cells that has value of say "1" or anything in it. There will be only 2 numbers at any time in any row that has the value in it. All other cells will have value of "0" or can be changed to empty cells. So, the formula should check from say range "A3 to Z3" in row and for example it gets the first numbered cell in C3 then it start filling color from C3 cell until it reaches the other cell in that range that has the number in it. Say the row "A3 to Z3" has 1 number in C3 and other in cell H3 then it should fill color between this two cells from C3,D3,E3,F3,G3 and H3.
View 7 Replies View RelatedI 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.........................
I have an if statement and if it's true I would like to have the cell turn black as well as mark it with an "x"
View 7 Replies View Relateda formula that you can you add cells with a certain color text only?
View 14 Replies View RelatedI have a table with changing lengths of the rows and columns. At the end of each row/column there is a cell with the sum. I want to put some color to it and make some additional formatting - have some borders around the main table and different colors for the empty and non-empty cells. I have the following
View 14 Replies View RelatedIs there a way to sum rows by color other than creating a new function?
View 5 Replies View RelatedHow can I format for a + or - for positive negative numbers and also have positive numbers be green and negative numbers be red?
View 2 Replies View RelatedI have a drop down box that shows progress of a project. The options in the drop down box are 0%, 25%,50%,75% and "Completed" - I am wanting to make it so when i click the desired option in the drop down (say 25%) it makes 25% of the cell show green (or completed)...all the way to when I click "Completed" then it will make the cell 100% green. I am trying to show a percentage of something getting done by way of color.
View 6 Replies View Relatedi had a worksheet with data running into 30,000 rows.i want to color the blank cells according to the item type;
For instance, if the item type is LIBRARY BOOKS;the column D(Reference 1) should not be empty or Blank.It should contain the word LIB.similiar for other types. I want excel to color the blank cell depending upon the item type.can i achieve this using normal worksheet functions or VBA is necessary.
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.
I have this scenario which i need to automate it. I have 5 colums P1,P2,P3,P4,P5. It contains W or L combinations.
In my Column F i Concatenate(A2,B2,C2,D2) together and it follows on for the rest.
Now i have some criteria which i am going to search in my Column G. In Cell I1 i will list my combination to search. It can be as follows :
WWWW
LLLL
WWW
LLL
So what i need to do is search my F column for this possible match. If it is a match I need to color the my Player 1 to Player 5 for that match. I have shown in my example inside the excel. I know the formula for how to find for matches using my combination. But i got no idea on how i can color it with the respective colors automatically.
Yellow = all four W
Red= all four L
Green= will have 3 W and one L
Blue = have three L and one W
I am using Office 2007 and have successfully used the Cells.Interior.Color to change cells fill color. I would like to be able to undo these changes. Depending on the data entered I am changing the color but I may need to change it back to white. When I use the Cells.Interior.Color = vbWhite the cell fill changes to white but the cell border is gone. Is there a way undo the color change and have the cell border show up?
View 2 Replies View RelatedI know there's no way of autofiltering colors with Excel 2003 but it can use function in VBA to identify the colors like 3 for red, 6 for yellow and so on.
I want to be able to insert a button on the "Summary" sheet to asign with macro that canautofilter the numbers in color cells in the same column. Also with the codes I've put in VBA gave out strange numbers on blank cell which I don't want these awkard numbers to appear after typing in =GetInteriorColorIndex(whatever the cell I type in).
How to color the background cells using text ...
View 6 Replies View RelatedI have this fantasy footy comp I run and every week I enter player by player scores. To make it easier for me I need to a strange formula. Basically can you make a formulua to sum cell based on text colour?
Every week the 3 players with the highest scores get votes. Eg highest gets 3 votes, second highest 2 votes and 3rd highest 1 vote. After each game I look thorough the players and change the colour of the 3 best to make them stand out. Green on black for highest, yellow on black for 2nd highest and red on black for third highest.
Is there a way to at the end of each week to have a the cell (for example cell v43 in my sample) to update or add up all the 3pts 2pts and 1pts that player has accumulated throughout the season?? In my example I have just hand counted them and inputed them.
I highly doubt it but might as well ask, for example if I have 10 cells some with odds and some with even can I make all odds have a certain FILL color and evens be another FILL color? Is there a formula for such thing possible?
View 4 Replies View RelatedI 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.
I have a column of cells formatted with different colors to represent a range of values. I want to know if that a formula or VBA I can use to I sum up these range of cells with the same color.
View 10 Replies View RelatedI want my VB6 app to conditionally color some cells. How can I turn a cell red if the value is below zero and green if zero or above?
I want to use a dynamic formatting rather than hard code the color since the values will be edited in the spreadsheet.
Can I do this as a range? e.g. A2:A500
Tried to record a macro which needs to color the cells which contains the following text, "Next mail needs to be sent", the problem which i am facing is; its currently coloring the cell which was recorded in the macro, which is G46, but i want all the cells which contains the above text to be colored.
Sub color()
Selection.AutoFilter
Range("A1").Select
Selection.End(xlToRight).Select
Selection.AutoFilter Field:=7, Criteria1:="=*Next mail needs to be sent*", _
Operator:=xlAnd
ActiveWindow.SmallScroll Down:=-3
Range("G46").Select
Selection.Interior.ColorIndex = 50
End Sub
I have a code that updates data from one file to another.....esentially, i run this code daily and it updates to a compiled daily log of all of the previous days data.
Currently, i am manually coloring the cells to visually assist in grouping what data has been uploaded for each day.
Sub UpdatePOQntyReducDB()
Dim lookupfilename As String