List Building Based On Cell Value Or Colour
Jun 18, 2014
I am trying to create a Macro/VB code that will build 4 lists based on a list (see attached example).
I have a list of applications and the numbers of users having them installed on their computers, these applications are marked using a RAG status (Red, Amber, Green & NBR No Business Requirement) on a seperate work sheet i want to split them by these RAG statuses, the status of applications changes as they are packaged for SCCM, so the RAG status will be changed, so i want the code to be able to rebuild the lists at any time to obtain an accurate view of the numbers at each status.
View 6 Replies
ADVERTISEMENT
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
Feb 26, 2014
I'm trying to use Excel to build a text file based on values from certain cells in Excel 2010. This is based partially off of static text that never changes and variables that will need to change. For example, I want Excel to output a text file that has the following text: The red fox jumped over the $X twice. I would want $X to be replaced by the value of A1 of the active worksheet. I will also have several lines like that, so it won't just be a single line, but anywhere from 20 - 120.
View 1 Replies
View Related
Dec 15, 2009
I want to build a file(name) list of all files in a directory including the ones in all underlying subfolders on any level.
E.g. make at total list of all possible files (not folder-names) in the given directory.
I have a clue, but I just manage to list the files in the first subfolder level - not proceed "deeper" in the folder tree.
View 9 Replies
View Related
Oct 3, 2012
As far as I can tell Conditional Formatting will not work for this problem.
What I need now is some code to colour the rows in the data sheet ("Standards_Data"), so some other code (which is working fine) can then colour data points on a multiple graphs (which will be on the "Graphs" sheet). I've played around with as much code as I can find but nothing has the flexibility that I need or doesn't seem to work at all.
The idea is that users can adjust the colours using the Column A on the "Graphs" sheet to best highlight certain things. Also this list could then be edited as old Standard ID's become redundant and new ones come on line.
So in a nutshell: Colour rows in "Standard_Data" based on value of Standard ID (Column 4) according to the corresponding colour found in the list in the "Graphs" sheet.
I've attached a trimmed down version of the workbook.
View 4 Replies
View Related
Jan 25, 2010
I want that Excel writes in a row the a sequence of musical notes corresponding to a certain tonality;
I have in one cell the musical notes, which are validated data that comes from a list and I could pick one from this list:
C C# D D# E F F# G G# A A# B
chosen one of the notes, excel would write down in a row the corresponding chromatic scale for that note. example - choosen A:
A A# B C D D# E F F# G G#
As you see, the scale begins with A, and then when it gets to the end of the list (tone B) it goes back to the beginning (C). In other words, what I want to implement itīs sort of a circular validated data.
View 46 Replies
View Related
Sep 27, 2006
I've built a spreadsheet that accurately displays my company's price list. However, from time to time, there are increases and/decreases, by percentages. I would like to know how I can build a formula that would allow me to quickly update the pricesheets by the appropriate percentage, without having to manually do so, one cell at a time.
View 2 Replies
View Related
Feb 3, 2014
I am trying to automatically construct a unique consolidated lists from several other lists. I want this to be formulated in such a way that the mother list is uptodate and reflect all changes made to the subsidiary lists. I have attached an example of what I am trying to achieve.
View 8 Replies
View Related
Sep 24, 2009
I have a userform that outputs data to a worksheet. One of the textboxes contains either one of 3 values, either: N/A, Debit, or Credit. This textbox is called "tbDebitOrCredit".
What I need is, when the the user clicks the "Submit" button and the data is output to the worksheet, the cell that recevies it output from "tbDebitOrCredit" turns a specific colour based on the value.
For example:
N/A = White
Debit = Red
Credit = Green
View 2 Replies
View Related
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
Jun 18, 2008
Can you sum cells based on colour, and if so, how do you determine what code/number your colour is to input into the programming?
View 13 Replies
View Related
Sep 21, 2012
I'm trying to change the colour of a row based on the value in a cell (in a column) and am wondering how I can do that. Conditional formatting doesn't allow enough colours unfortunatly. Ive googled it and can only get a cell colour to change not a row colour.
To give an explanation:
Sheet 1: Rows 5 - 100 colour to change based on Sheet 2 Column K5-100.
Column K5-K100 will have values between 0 and 10.
View 4 Replies
View Related
Mar 20, 2013
I am trying to establish whether there is a excel formula which will allows you to just to count the cell with the same cell colour. I have made the below listed example to illustrate what I am trying to achieve:
A B C D E11/03
12/03
13/03
14/03
15/03
Total
[Code].....
In the there are total of two green colours, and one red colour to be counted.
View 3 Replies
View Related
Mar 21, 2013
I have a auto generated report that colours a certain column, that shows all the items that are in receiving error. I want to pivot the data and show the break down on the colours used; Green = newly added, Amber = escalated, Red = urgent
at the moment I'm adding a column next to it and filling it in myself. is there away to automate this? either via a custom forumla or marco
View 1 Replies
View Related
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
View Related
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
Aug 5, 2013
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"
View 1 Replies
View Related
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
Jun 28, 2014
In the xls for each step I have 2 raws-planned and actual. Step planned duration is populated manually over the weeks.Before that row we have another reflecting the actual step status per week
I would like to find a way how cell reflecting the actual status of a step can be automatically populated (coulored) based on the colour/value of activities that are planned for that week and for that step.Activities are listed below the step and again have planned and actual row.
The rule should be : if for a week we have several activities all of them should be finished in order to have step stataus auto populated as green. If a single activity planned for that week is not done-then weekly step status should be red.
The activities for each step are grouped below the step. It seems that one of the difficult part in that request would be how formula will understand where starts and finishes the activities that belong to one step. To get that happen I placed a column showing step and another column where we activity.
View 9 Replies
View Related
Jun 30, 2009
Using 2007, I need to conditionally format a cell colour based on 5 other cell colours that have been conditionally formatted. The 5 other cells will be coloured either red or greem. What I want to do is have an overall status cell that would be show green if all of the other 5 cells were green, amber if 4 of the other cells were green and one was red and red if 3 or more cells are red.
View 4 Replies
View Related
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
Mar 22, 2011
I have a circle object on an Excel spreadsheet(I used "Insert", "Shapes" to put it there).
I would like to change the colour of the circle object based on the contents of cell A1.
Cell A1 will be "Red", "Yellow" or "Green".
If I type "Green" into cell A1, then I would like the circle object to change to green.
If I type "Yellow" into cell A1, then I would like the circle object to change to yellow....
View 9 Replies
View Related
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
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
May 3, 2007
I have inherited a spreadsheet from someone else that rather cleverly (in my humble opinion) sets the colour dependant on the contents. ie negative value is red, zero is amber and positive is green.
i like the idea and would like to use it on a spreadsheet of my own, but have tried to find out how it is done and can't!
View 9 Replies
View Related
Mar 21, 2008
I would like to change cells A2 to G2 to a different colour based on the value of G2
(0 to 48) Dark Green
(-2) Light Green
(-4) Yellow
(-6 to -48) Red
The range of the sheet would be A2 - G130. Each Row would change on value of cells in Column G.
View 9 Replies
View Related
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
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 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
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
