I need to set a cell colour by the value of a cell in another sheet so i can't use conditional format. Is there a formula that allows the user to set a cell style, colour, font etc.
View 9 RepliesIs it possible to use a formula to shade a cell dependent on a condition? I have tried an if formula (see below) but it is incorrect. =if('November 2006 SVOC'!B6>'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [red],if('November 2006 SVOC'!B6<'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [blue],))
View 3 Replies View Relatedhow to change the cell colour due to a sum increase or decrease? For example: if the amount is greater than 200, change cell colour to green. If under 200 change cell colour to yellow.
View 2 Replies View RelatedI work for a UK charity and have a list of funders in an Excel 2007 spreadsheet.
One of the columns refers to the date on which a new application for funding can be made to that particular funder.
In many cases new applications for funding can't be made for 1 or more years since the last application - sometimes as many as 5 years later. How to get a cell to refer to the date that it contains.
For example, say I have in cell A1 "The Acme Funding Organisation" and in cell B1 (i.e. the "Reapply when?" column) a date of 01/04/2013 (British date format, i.e. 1 April 2013) then what I want Excel to do is to look at the date in cell B1 and if that date has been reached to highlight the cell red. That way I'll know that the reapply date has been reached & that a new application can be made.
I am trying to create a sheet in XL 2010.
In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.
Column B is when the weekly inspection is due.
Column C is when bi-weekly inspection is due.
Column D is when monthly inspection is due.
Column E is when 6 monthly inspection is due.
I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A
I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.
For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.
A1
B1
C1
D1
E1
Inspection Date
Weeekly Due
Bi-WeeklyDue
Monthly Due
6 Monthly Due
January 1 2013
Change red Jan 8 & return blank Jan 10
Change red Jan 15 & return blank Jan 17
Change red Feb 1 & return blank Feb 3
Change red June 1 & return blank June 3
Ive also attached the worksheet
I am trying to create a sheet in XL 2010.
In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.
Column B is when the weekly inspection is due.
Column C is when bi-weekly inspection is due.
Column D is when monthly inspection is due.
Column E is when 6 monthly inspection is due.
I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A
I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.
For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.
A1
B1
C1
D1
E1
Inspection Date
Weeekly Due
Bi-WeeklyDue
Monthly Due
6 Monthly Due
January 1 2013
Change red Jan 8 & return blank Jan 10
Change red Jan 15 & return blank Jan 17
Change red Feb 1 & return blank Feb 3
Change red June 1 & return blank June 3
I need to write a condition in such a way that cell IF A1= "DBR","BPR","SLR","SKR" then change cell B1 to SPECIFIC COLOUR Each condition different colour. A1 values ("DBR","BPR","SLR","SKR") is a list of selectable values. if the condition is falls then B1 should be normal (white color cell) with 0 in it.
View 1 Replies View RelatedI am trying to make the colour of cell change depending on the result in an adjacent cell and have found and adapted the following:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("x4:x202")) Is Nothing Then
Select Case Target
Case 11
icolor = 43
Case 21
icolor = 43
Case 31
icolor = 27
Case 41
icolor = 27................
I have VBA code that changes the cell background colour in column A based on criteria in column B. What i want to do is select a cell in the same row in a range column O:AB and change the background colour to the same as column A. This action is for rows 4 to 41.
Basically i just want to click in a cell and for that cell's background colour to change and match the colour in column A.
I dont have that great knowledge in Excel but i wanted to try and run a
macro in an IF formula but realised that was not possible. I have now got a
formula that gives a value in a cell when i want the whole row to turn to
red, but im not sure how i can get a macro that will change the whole row to
red when that cell shows a value. Can anyone help on this matter?
I am tyring to do conditional formatting whereby if anything is entered in a cell i want the colour to change to say yellow and if nothing is entered just to leave blank.
View 9 Replies View RelatedHow can you change the colour of a cell (A1) using that number
eg
1=red2=orange3=white4=light blue5=Blue12345
OR
Make the cell with numbers above 0 go red and all cells with numbers below 0 go blue
I have a table which says that this is the amount of coloured cells we have in another sheet.
For e.g.
Field Name Code A
Item Description 5
Now, the item description column has 5 cells in another sheet which are filled in with "Yellow" Colour. So what i want to do is to click on this 5 in sheet 2 in this case as per the attached sample which takes me to the filtered result on sheet1 of 5 yellow coloured cells under the column of Item description including an additional filter of Code "A"
If A1 contains 'good' then row A = red
I need an entire row to change colour if a particular cell in that row contains one particular word.
I am making a little Formula 1 Grand Prix spreadsheet, and would like to know how do i enter a number into a cell and the cell changes colour.
[Code] .....
So if i type when of these in a cell i want to to change to the colour corresponding to the chart.
I have the code below which fills a cell when doubleclicked. I would like to modify this to altenate between 3 different colours on each doubleclick.
View 2 Replies View RelatedHow I can change the background colour of cell C:2 depending on what other cell i click? Below a sample of the code im using Basically when i click B:2 i want C:2 to turn grey with the text on top.... Then when i click B:4 i want C:2 to turn Red.....
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case Is = "$B$2"
Range("D2").Value = "No action needed connector is down" 'change this to suit
Case Is = "$B$3"
[Code].....
I would like to have the colour of a cell change within a spreadsheet anytime that a change has been made. I have used the worksheet_change function to change any text that has changed in a cell, however I would like to have the actual cell colour change if someone deletes anything from that cell. I figured that I could use the worksheet_change function for that as well however it is not working.
View 9 Replies View RelatedI dont understand how this event works.
I want a certain object to change colour when a certain cell = something.
I have 5 cells in the same row (C20:G20)
When i click on one of them i want it to either change the background of the selected cell or the background of the cell below it.
EG if i click on C20 either C20 or D20 the highlight black and change the text colour to white, or just change the background to grey.
i thought it might be something like Target.Offset(1, 0).BGColor = 6
i tried this>
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells = C20 Then
Target.Offset(1, 0).BGColor = 6
End If
End Sub
Trying to have this embedded in the worksheet private sub change section. I want it to change the value of these ranges when the target has a value in it. I'm getting an error though:
If Target.Address = "$E$27" Then
If Target.Value "" Then
Range("I25:J26,P25:P26,R25:S26").Interior.ColorIndex = White
Else
Range("H25:V31").Interior.ColorIndex = 15
End If
End If
I've found a great thread that answered part of my problem (Change colour of text on update of cell)
This code changes the colour of a cell when selected. With the application I'm trying to make I need this effect to only occur in a certain area say A10:D30
I'm trying to make a scheduling page that uses timebars to represent when people are working. I though if a user could click or highlight the hours and a time bar produced I could lookup to see what hour the timebar starts and ends and show the hours worked. I also need it to cycle through 5 different colours before returning to white.
Private Sub worksheet_change(ByVal target As Range)
target.Interior.ColorIndex = 4
End Sub
I can't use code for this problem as I need to give this to someone who doesn't know VBA. They will need to use it in several different reports, so I can't produce something in VBA very easily. I have three worksheets, Summary, Year1 and Year2.
I want to display either Year1 or Year2 data in the Summary sheet depending on what the user enters in a cell in the Summary sheet. For example, if the user enters "Year1" in cell E2 then I want to point to a cell in the Year1 spreadsheet.
I tried using formula: =E2 & "!B3" but this doesn't work. Is there another solution? I have attached an example.
Is there any way to change the border color of a cell by running a subroutine? Here is my example:
Col 1
Col 2
1
2
3
4
I have an existing table with data that is updated daily ... more rows are added. Currently, the cell border is black. I would like to run a macro such that the column header is shaded grey and the borders turn into a shade of gray. I am interested in the 35% grey.
I have 2 columns of figures. One column has a defined name of 'Estimated Value' and the other column 'Actual Value'
My IF statement should simply read "=If(Estimate>Actual,Text color RED, Text Color Black)
My attempts at finding the code for the True/False statements are legion and 'Conditional Formatting' does not appear to work
I am trying to make a cell change colour based on the partial content of another cell.
For example, cell A4 has a alpha-numeric sequence in it and I want cell B4 to turn blue if part of the sequence is ABD or F&C.
I am looking to change a row colour if any type of data is entered into a specific cell.
Context: In column H entitled 'Start Date' when someone enters a date n any format into this box, I want the whole row to change colour to easily see what projects have started and what ones havent.
I want to change the colour in a cell depending on the sum of two other cells.
ie
A3=1,B3=1
A4=2,B4=1
A5=1,B5=0
A6=2,B6=4
So i want if
A3+B3>2 THEN C3 TO HAVE GREEN COLOUR
A4+B4>2 THEN C4 TO HAVE GREEN COLOUR
A5+B5>2 THEN C5 TO HAVE GREEN COLOUR
A6+B6>2 THEN C6 TO HAVE GREEN COLOUR
SO IN THE END ONLY C4 AND C6 WILL HAVE GREEN COLOUR.
Is there any formula to do so with conditional formatting?
I am trying to devise a service schedule that has the installation date in say cell D4 and I would like this to automatically place a date a year from there in to cell E4, then 4 weeks before date comes round i would like the text cell to turn yellow, then on & after the date to turn red. Is this even possible?
View 12 Replies View RelatedIs there any way to change the pattern color of a cell (or a group of non-adjacent cells), say from No Color into Red if the cell(s) protection is locked (or unlocked)? Seems to me that the Conditional Formatting cannot work this way.
View 3 Replies View Related