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
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.
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.
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 RelatedI'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.
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.
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
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.
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 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"
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 RelatedIn 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.
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 RelatedI 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 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.
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....
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?
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] ......
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!
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.
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.
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.
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" .
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?
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]......
I wonder if the following is possible in the worksheet_open event. I want to be able to open my worksheet and see via a message box all the important features I need to look at.
Using column AG,
Take the cells that are conditionally formatted to red, take the corresponding cells in column A & B and output this
information in a message box.
I have a workbook called "Staff Planner". I need to check Columns C to I in Sheet "Plan" to firstly find text in a red font, and then copy this value to Sheet "Useage" in Column K. (So, search C1:I1, find red font, copy contents of cell, paste value to K1). I then need to repeat this with blue font/value to H1, green font/value to E1, and finally pink font/value to B1
View 9 Replies View Relatedcreating a cell to show: no colour unless
date minus 90 days as amber
date minus 45 days as red
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]
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.