Excel 2007 :: Get Cell To Refer To Date That It Contains And Change Cell Colour Based On That?

Dec 19, 2012

I 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.

View 2 Replies


ADVERTISEMENT

Excel 2010 :: Change Colour Of Cell Based On Date In Other Cell?

Jan 10, 2013

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

View 1 Replies View Related

Excel 2007 :: Current Time Causing Cell To Change Colour

Jun 4, 2014

I am creating a spreadsheet in Microsoft Excel 2007 which holds information about the airline Emirates which I have created using information from the internet. I am currently creating a sheet which has all their flights in and holds information about the departure time and arrival time of the flight and the type of aircraft ect. I am wondering if there is anyway in being able to have the cell which has the flight number in to change colour (Green or Red) if the flight is in the air or not by using the departure and arrival times already set on the sheet. I am wondering if you possibly need to have a live time on the sheet so that it can work with that .....

View 1 Replies View Related

Excel 2010 :: Change Cell Colour Depending On Date In Another Cell

Jan 9, 2013

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

View 3 Replies View Related

Change Cell Colour Based On Number Of Days Before Date?

Jan 17, 2014

creating a cell to show: no colour unless
date minus 90 days as amber
date minus 45 days as red

View 6 Replies View Related

Excel 2010 :: VBA Code To Automatically Change Colour Shape Based On Cell Value

Mar 2, 2012

I have a peice of code that i know is inefficient and it is in danger of becoming too large. I have a spreadsheet that has circles aligned to each cell. There are around 100 in total. The code changes the colour of the shape based on the cell value in which it sits. However, the code needs changing and also it does not automatically update the colour shape even though the cell value changes. I have to manually select a cell and then the formula bar and then press return for it to update.

I am using excel 2010.

This is the code i am using for each shape.

Code:
If Range("n12").Value = text Then
ActiveSheet.Shapes("Oval 250").Fill.ForeColor.RGB = RGB(255, 255, 255)
End If

[Code]....

View 6 Replies View Related

2007 Cell Colour Change

Mar 4, 2010

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

View 9 Replies View Related

Excel 2007 :: Cell Color Change Based On Values (Range)

Oct 2, 2011

How to change the background cell color based on value ranges(s)

I tried conditional formatting but it works between two values only, in my assignment I want to show:

River levels in relation to flood class
>=2m =2.6 =3m major flood (background turns red)

I hope it is possible in Office 2007

View 9 Replies View Related

Excel 2013 :: Change Text In Cell Based On Date In Another Cell

Apr 14, 2014

I have the the following spreadsheet: [URL] My formula in Columns A to E is the following:

=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE))

I would like to do the following in Column A: If the date in Column C is greater than today, then Column A must also say "Branch Not Open". I have tried the following:

=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),IF(C:C<=TODAY,"Branch Not Open, IF(AND(C:C=>TODAY,VLOOKUP(Query1_2[@Branch],Master!A:I,9,FALSE)))

But Excel does not like the formula at all.

View 1 Replies View Related

Refer To A Cell And Pull Value AND Colour?

Feb 18, 2012

I have a cell containing a 'bullet' symbol (•) and a cell containing text.

If I make the bullet red and the text is black is there a way I can combine them in the same cell and the bullet remains red and the text black?

Essentially the situation Im in is I have a load of text I need to add coloured bullets to and I dont want to individually paste in and colour format all the bullet points because it would be too time consuming.

View 1 Replies View Related

Change Colour And Values Of Cell Based On The Value In Adjacent Cell?

Nov 10, 2012

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 Related

Excel 2007 :: Change Colour Settings?

Oct 9, 2011

Is there any way to change the settings in excel 2007, to give me the original color palate from 2003? I want to use the old colors because I don't like the new ones.

View 2 Replies View Related

Change Colour Of Cell Based On Text In Another?

Feb 1, 2013

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.

View 1 Replies View Related

Change Colour Of Autoshape Based On Cell Value

Mar 21, 2012

I've seen examples of VBA that will colour autoshapes based on cell values. I have a map of the UK made up of autoshapes that have been edited to create a shape of each postcode area such as EX, GL, NR etc. Also I have a list of all these postcode areas and I have given them a zone number (1-7) depending where in the UK they are.

PcodeZoneDH3DL3DN2DT1DY2E4EC4EH5

At the moment I have set the colours using the fill option so anything that is a particular zone is a certain colour (i.e. zone 2 is green).

What I would like to do is to set a zone to a colour, so the map will update all the zones with the right colours.

I need to be able to read the zone number that is assigned to a postcode area and change the relevant autoshape to the correct colour so that any postcode area can be one of 7 colours.

I can't attach a spreadsheet with my map and postcodes otherwise I would have.

Is there an easy way i can read the zone number of each postcode area in the list and update that autoshapes colour? Would the autshape need to have a name so it know which one to update or is this not possible?

View 9 Replies View Related

VBA Change Cell Colour Based On VLookup

Oct 22, 2012

I have found this code that, with changes, I can make work with the cells and columns I am using. What I cannot see is how to make more than one cell change colour rather than just the cell in column J.

How can I change the code below to change several cells in the row? I will be using cells in columns A:F.

Data will be entered into the rows from A2 to E2, in F2 a vlookup will enter one of the values, "High", "Medium" etc.

Each of these values has a colour assigned. The code below, with alterations, will be able to colour the cell in F2 depending on which value is entered. I need the whole row from A2 to F2 to be coloured.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim ChangedRange As Range
Dim r As Long, iCol As Long

Set ChangedRange = Intersect(Target, Columns("F:H"))

[Code] ......

View 5 Replies View Related

Change Cell Colour Based On 3 Values

Jul 18, 2006

My question is i want a cell to have the colours:

Green=if within the max min range [as specified by user]
Amber=if the current value is less than the allowable slack level[again specified]
red=if the current value is less than or more than the min or max respectively.

View 4 Replies View Related

Change Cell Colour Based On Expiry Dates

Nov 13, 2013

I am trying to change the colour of a cell based on the expiry date. I know I need to use conditional formatting but Im having trouble.

Colomn E from row 5 to 1200 have dates in them from as far back as 08/2013. I need to change the cells so they turn yellow after 28 days and red after 45.

E.g. 1. E953 has a date 07/09/2013, I need it to change colour as above, 28 days and 45 days.
2. E961 has a date 04/10/2013, I need it to change as above, 28 days and 45 days.

Can I formulate the entire E column so when I put a date in, the formula will automatically start.

All the other formula's I've seen involve starting from "today" or "now" .

View 9 Replies View Related

Change Cell Colour VBA Based On Text String?

Oct 11, 2011

I need to change the colour of a cell using VBA based on the input of a certain word. I have used teh below code but it doesn't pick up different variations of the word

Dim cell As Range
For Each cell In Range("E2:E500")
If cell.Value = "Check" Then[code]....

This code works fine (i have put it into worksheet change) when I use "Check and Mark and Chase" but some users are bound not to use capitalised first letters and on these occasions the cell colours are not changing.

How to add something to thie code above to make it work for any variation of capitalisation?

View 2 Replies View Related

Excel 2007 :: Clicking Cells To Change Colour Format And Mirroring

Feb 24, 2012

(Using Excel 2007)

What would be the quickest and most efficient way of doing the following:

Example

Sheet 1: clicking once, twice, three times in the following cells to change the fill to Red, Yellow, Green respectively (in Row 22, COLUMNS E H K N Q T W Z AD AG) and mirrorring this format/fill on:

Sheet 2: , mirror the selected fill on sheet1 in E22, H22 , K22 in in E3:N3 respectively example E22 (sheet1) is mirrored now in E3 (sheet 2); H22 (sheet 1) is mirrored in F3 (sheet2) etc etc.

I have 35 tabs formatted in the same way - each tab, as with Sheet 1 is linked to a different row on sheet 2 in the same range as is sheet 1 and would need to replicate the mirorring on Sheet 1 to each of the other 34 tabs.

View 9 Replies View Related

Colour Cell Based On Date ..

Feb 11, 2009

It's quite funny that i'm asking this cause I answered a similar quesstion earlier. But I can't seem to get my head around the thinking of this one.

I am trying to create a formatting so that if todays date is greater than the date in the cell the cell is coloured red. And if todays date is > today()-14 <today() the cell is coloured yellow.

I don't think it is as simple as using conditionaly formatting, it may well be but like I said I can't quite get my head around what I need to do.

View 8 Replies View Related

Excel 2003 :: Conditional Formatting Not Working And Cannot Change Colour Of A Cell Or Writing?

Sep 23, 2013

Excel 2003

On one of our computers at work, Excel is acting especially strange. The conditional formatting in Excel on one of our computers is not working. In addition, we cannot fill a cell with any color or change the color of the writing. We can click a color for the letters and type it in, but immediately after another cell is highlighted, the writing goes black again. When we try to highlight multiple cells with the cursor, the cells are black instead of the normal color. This happens to not just spreadsheets that are new, but existing spreadsheets on our shared drive from only this one computer as well. No other computers are affected this way. All other computers show the spreadsheets fine and do not have any functionality issues.

View 5 Replies View Related

VBA To Colour Cell On Each Line Based On Date Match

Nov 25, 2013

I have a row which contains a date header.

Under this there are multiple lines of activities each with a different end date and empty cells under the calendar headers so that people can enter how long they have worked against that project.

What I want to do is to compile a VBA coded which will cycle each line and at the intersection of where the end date and header match to colour that cell in a defined colour.

An example below:

Date
Colour
01 Jan
02 Jan
--->

[Code]......

View 8 Replies View Related

Excel 2007 :: Create Bar / Count Weeks And Colour Bar From Input Date?

Feb 14, 2012

I am trying to simplify a type of gannt chart bar across a spreadsheet. The spreadsheet has dates across row 3 that are calculated from the first cell F3 with =F3+7 to populate the rest of the row with dates. I want to be able to input a start date in D4, then all the other titles in column A. This start date will start the coloured bar at that start date in the chart, I will then copy/drag that start date cell for however number of weeks for the duration and I want the finish date to auto populate E4 with the finish date and also auto populate the numbers of weeks in C4 for that bar.

Excel 2007ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANA
OAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZ
CACBCCCDCECFCG1D42342McArthur River Mine Power

[Code]....

View 9 Replies View Related

Excel 2003 :: Cells To Change Colour When Within A Due Date

Feb 9, 2014

I have a very basic understanding of excel, I mean BASIC, for my work I have multiple dates for vehicles to go in for inspections. I would like the cells to change colours when within 30 days, 10 days and 5 days. I'm on a work excel 2003.

View 6 Replies View Related

Excel 2007 :: Date Filtering - Change Date Format Supplied By Date Picker

Apr 24, 2012

I have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.

I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).

When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)

So my questions are:

1. Is this due to the formatting?

2. Is there a way to change the date format supplied by the date picker?

View 7 Replies View Related

Excel 2007 :: SUM Numbers Within Cell Based On Unique Strings In Another Cell?

Jan 11, 2012

how would you do the following in excel 2007:

"SUM numbers within a cell based on unique strings in another cell".

For example, how would i use formula to SUM the following numbers (and only the numbers for david and sam only once), 700+454+50+40+2+129+16

700david
700 david
454john
50buch
40daniel
2sam
2sam
129mike
16steve

View 9 Replies View Related

Excel 2007 :: Restrict Cell Input Based On Another Cell Value

Jan 22, 2013

I am working in both 2003 and 2007 Excel.

This is a simplified version of my worksheet:

I have two cells, A2 and B2. When I enter a numeric value into B2, I want it only allowed when certain text values are in A2. I.E.: Allow any value in B2, but only if A1 is either "SYDN or "ADEL".

Now using info found in an old post, I can achieve this with a combination of data validation and code:
=OR(A2="SYDN",A2="ADEL") for the validation and for the code:

[Code] ......

The only problem is I also need to reset B2 if A2 changes to invalid data as a result of some other input to that cell (A2). In other words if B2 is showing "1" already and A2 were to change from "SYDN" to say "LOND", then B2 needs to be cleared. If A2 changed to "ADEL" then it doesn't need to clear but it is ok if it does because it is fine to re-enter "1" into B2 again.

I have attempted to attach a sample worksheet : Xl0000003.xls‎

View 4 Replies View Related

Excel 2007 :: Change Colour Of Tabs To Match Other Tabs In Workbook

Sep 22, 2012

I have been trying to get Excel (2007) to change the colour of tabs to match other tabs in the workbook

Using this code I get the colour code of the current tab

Code:
x = ActiveSheet.Tab.ColorIndex

But when I use this value in a procedure like this:

Code:
ActiveSheet.Tab.ColorIndex = x

The tab is a different colour!

View 3 Replies View Related

Conditional Formatting - Change Cell Color Based On Date In Another Cell

Oct 9, 2013

I have two columns. In column B is the date of "last check". I column A is the date of "next check". I would like to have cell A2 in yellow color 334 days after the date entered in cell A3 and than in red color 365 days after the date entered in cell A3. Same thing for cell B2 related to date entered in cell B3. Yellow color in cells announces that check will expire within 30 days and red color that check has been expired.

View 1 Replies View Related

Excel 2007 :: Change All Matching Value Based On Number Change In Column?

Feb 19, 2014

I have a situation that I cannot get my hands on. I have a set of Project Numbers in my Column A. (Will Try to post an example).

I need that everytime I change a number in my Column B that it will change all the numbers, in my column B, based on the matching Text in my Column A.

Demo1.xlsm

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved