=If (forumla) = Colour Fill Cell
Oct 5, 2009
I have two dates lets call them cells A1 and A2. I then have another cell that caluclates the difference in these dates into a simple number (ie not another date) - A3. I need one of two things.
Ideally I want the whole cell (A3) to appear as a Green (fill) cell if the value is below 5, appear Orange if beween 6 and 9 and appear as red if its 10 or over.
I do not know if you can colour a cell that has a formula in already. So what follows is my current 'work around', which, if you can not colour the A3 cell, then I would need this cell coloured (A4). =IF(D12>7,"Urgent Attention",IF(D12<5,"New",""))
View 5 Replies
ADVERTISEMENT
Jul 20, 2009
Basically I have cells like this.
.....
I want to fill this formula down so that the summing occurs all the way down the collumn. However when I fill it down, it just copys the first two forumlas, rather than make the formula correspond to the row.
View 9 Replies
View Related
May 29, 2014
I need to fill 2 other cells with varying colours dependant on what is chosen within another cell.
I have a drop-down list in cell A2 to select either ON SITE, ACTIVE or CANCEL.
What I need then basically is...
if A2 equals 'ON SITE' then fill other two cells (A3 and B1) GREEN
if A2 equals 'ACTIVE' then fill other two cells (A3 and B1) YELLOW
if A2 equals 'CANCEL' then fill other two cells (A3 and B1) RED
[File attached]
View 8 Replies
View Related
Jun 20, 2014
I have a calendar set up with dates going along P6:CU6. Column C9:C27 has various status' for the job such as "Complete" or "In Progress" etc. Next to that in rows D and E I have start and end dates for the job. What I am trying to do is change the fill colour of the empty cells that are in line with each job, so that between the correct dates they are the corresponding colour to the status. So a complete job on row 9 that started on 26/05/14 and finished on 28/05/14 would have three green cells along Q9, R9, and S9.
I have tried an IF/AND/OR statement which looks like this:
=IF(AND(Q6>=D9,Q6<=E9),OR(IF(C9="Planned","P",""),IF(C9="In Progress","IP",""),IF(C9="Late","L",""),IF(C9="Complete","C","")),"")
I am getting a #VALUE! data type error, I have tried reformatting the cells to various date formats but no luck so far. This could be because my dates are set up as =P6+1 etc. but I am not sure. The error only occurs when the cell falls within the dates, otherwise it remains blank.
I intend to hide the value in the cell and use conditional formatting to change the cell colour based on the hidden input.
Once this is working I also need to take weekends into account, column G has any 2 value combination of Y and N to say whether the employee is working Saturday, Sunday or both. Row 7 has a "1" value underneath every Saturday and a "2" underneath every Sunday. I can imagine how this would work but combining it with the above formula could be complicated.
View 1 Replies
View Related
May 27, 2014
if I have one cell that ive enlarged to half the sheet.... and it is effected with rollover hyperlink so that when I hover over other cells. it turns different colours..
is it possible to make that cell open up different files using the double click event depending on the colour it currently is?
i.e. if its blue, open file1. if its yellow, open file 2 etc
View 4 Replies
View Related
Feb 1, 2007
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 13 Replies
View Related
Oct 27, 2007
way to highlight selected cells if one of the selected cells equals a certain value eg
A B C D E
1 X X X Y X
2 X X X X X
3 X X X Y X
I select cells A1-E3 and if column D = "Y" then colour fill the row from A to E
View 9 Replies
View Related
Oct 2, 2008
I have a table:......
I want to colour in the relevant cells for each ref depending on the numbers in the stage column. So for the first line, the cells in columns 1,2,4&6 need to be shaded in. For the second line, the cells in columns 1,2,3,4,&5 need to be shaded in. I already have a lot of conditional formatting set on the sheet so need to do this with a macro.
I have tried to write something but am having issues with it finding the 1 and shading in the correct column but then jumping out of the loop and on to the next cell.
View 4 Replies
View Related
Nov 28, 2006
I'm looking for some code to change a cell's fill colour
eg. by selecting an individual cell in a range, say A1, the colour of a cell, say K1, in a corresponding range changes to yellow
or if I select multiple cells, say A1:A9, the colour of cells K1:K9 change to yellow
View 9 Replies
View Related
Feb 19, 2008
I have a worksheet with pricing & part numbers on sheet1. I put a formula on sheet2 that puts the lowest price in colum C of sheet2. Now I need to also get the part number from the cell before. So if the lowest price is in cell E3 I need to also get the part number in D3, and put it in B3.
View 9 Replies
View Related
Oct 5, 2013
If there is a colour in a cell that I want to use in another cell how can I find what the colour is, and/or copy it to the new location?
Sometimes a colour other than those available as a "standard" colour is used and it is almost impossible to match it by trial and error.
I realise that the Format Painter can be used but I often just want the colour only and not have the rest of my format altered.
View 2 Replies
View Related
Sep 12, 2013
I want to gradually change th colour in a block of cells. my plan is to use code to sequentially fade through ten or so shades from light to dark. is there a way to slow the code down so that it takes a couple of seconds to run the sequence?
View 2 Replies
View Related
May 20, 2009
I basically want for it to be able to calculate all the games a each team has won in the example below.
View 13 Replies
View Related
Apr 22, 2014
I have a spread sheet with various tick boxes, that when ticked calculate an accumulative percentage in a cell. This cell is the basis of my graph. e.g. if the cell displays 80% - the chart with show 80% - simple.
however, I want to write a vba code that changes the fill colour of the chart depending on the percentage.
i.e. if the percentage data = 0-49% I wish the chart to display as red. 50-69% - yellow and 70%+ = green.
View 4 Replies
View Related
Dec 5, 2013
Code:
ActiveSheet.Range("A1:G40").Interior.ColourIndex = 6299648
I am trying to fill the above range of cells with a colour, but keep getting an error message.
View 3 Replies
View Related
Oct 5, 2007
Column A = Dates in ascending order
Im after the code for searching this coloumn for 8 dates i have listed elsewhere and then i would like it to apply rose colour just to the dates in column a.
how do i link with specified dates?
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
End With
End Sub
View 9 Replies
View Related
Apr 2, 2007
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
A B C
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 14 Replies
View Related
Jan 14, 2014
I'm recording a macro, so a line in my graph is a specific green. The marker fill won't retain the green, it always ends up blue. the lines and marker line is green. no matter what color I try the marker fill will always be blue. How to correct this?
View 6 Replies
View Related
Aug 12, 2006
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 7 Replies
View Related
Oct 3, 2012
I would like it to loop through all the charts on the "Graphs" sheet.
It works well but gives a "Run-time error '1004'" Application-defined or object-defined error afterward that I would like to get rid off.
VB:
Sub Colour_Chart_working()
Dim rngColors As Range
Dim rngCell As Range
[Code].....
View 1 Replies
View Related
Apr 17, 2014
I have a lookup that gives and RGB colour code in Cell A1; for example 186, 206, 140.
I'd then like Cells R10:V15 to fill with the RGB colour based on the result in A1.
View 2 Replies
View Related
Jan 19, 2009
i have 4 areas north east south and west, what is the forumla to enter into a cell that would automatically add the Salesperson when you know the area
Table is
Date, Area, (FORMULA), Amount
13/11/09, North, ( ), Amount
Basically there is a table with the salesperson per area
if i was writing it i would say its as follows
If Area = North then Mr Smith else if Area - South then Mr Jones etc etc
View 9 Replies
View Related
May 23, 2014
A column of cells can be 1o different shades of green according to their value (achieved by using conditional formatting.) I want to arrange that at a certain time all the cells of a part of the row of a formatted cell are the same colour.
View 4 Replies
View Related
Mar 22, 2009
In 1 cell i need a forumla to give me a starting number and take one away each time a code or codes are dislayed in a cell range. Something like this
A1 = 23 days or any number days i needed
Cell range B1:C52
every time a selected 1 or 2 letter code appears in the cell range i want A1 to subtract 1 day from the starting number, i would need it to subtract half a day if one code appears ie HD, the codes may be P, OT, HD
View 4 Replies
View Related
Apr 25, 2007
I need a formula showing that if a number is less than say 1.25 then it rounds down to 1.0 and if its between 1.25 and 1.75 then it rounds to 1.5 and then if greater than 1.75 then it rounds up to 2. I need it to work for all numbers not just 1.
View 10 Replies
View Related
Jun 18, 2009
I have a worksheet I use at work that calculates volume proccesed hourly, and also shows a running total as you enter hourly figures into the table, my question is can you hide everything in the result cell until the figures for each hour are entered ? currently all the result cells have irrelevant numbers in them until you enter the hours figure and the formulas do there thing, I could relly do with them being blank until the relevant hours information is input?
View 2 Replies
View Related
Aug 19, 2009
I am trying to do is use a combination of IF and SEARCH forumlas to return a result based on a choice of constants available to the user. Issue 1: IF Function to determine which worksheet to SEARCH What I would like to add to this is if one of the 3rd criteria (Amount) is under a certain number then the results are derived from the 'Staff' worksheet'. (This is stage I have gotten to so far). However, if the criteria is above a certain number then the results are derived from the 'Executive' worksheet. This is the part I'm so far unable to do.
Issue 2: SEARCH function returning the column per the user's selection on 'Matrix' page In addition, criteria 2 (Type) can be selected which corresponds to a column in the Staff and Executive worksheets. So far I have been unable to get the correct search results to post in the Matrix page. What I mean by this is that currently it is set to Type1 only. I have tried using an IF function in cell G8 and include this in the formula, however, the forumla returns the contents of G8 in the cell instead.
Issue 3: SEARCH results are post in an accending order. At this stage I have only been able to work how how to do this by using an AUto Filter on the Staff and Executive pages and sorting accending that way. Going forward as updates are made to the staff and executive pages I would prefer that this manual task was not required. Instead, the formula used returns the search in accending order.
View 4 Replies
View Related
May 31, 2007
If you Have a list of account numbers and amounts, is there a way in one forumla to get the average per account?
View 9 Replies
View Related
Aug 8, 2009
I have a sheet which calculates payment amounts.
Column titles:
Hours | Rate of Pay | Total
In the hours column usually the entries consist of numbers and everything works fine. However when an employee is on holiday they are still paid.
What I want to do is be able to enter the letter "H" for one of the entries in the hours column. The sheet to translate this as 2 hours.
H=2 x rate of pay = total
I cannot for the life of me get the correct formula to in order to achieve this. I don't particularly want to use a macro for this and others have suggested the "COUNTIF" function.
View 10 Replies
View Related
May 27, 2008
I have the margin and I have the sale price. How do I figure out the cost?
View 12 Replies
View Related
