Trying to run a VBA to colour a range of cells based on the input of a name. I can write the basic level VBA to colour one cell by numerical input but struggling to see how this can be adapted to colour a range of cells and if instead of 1,2,3,4 etc the case could be replaced as text: eg Joe, John, Jason etc.
View 9 Replies
I am trying to get spreadsheet cells to change colour relating to arrival and departure dates. I also need the cells to be assigned value so I can use them to calculate cost based on number of nights, eg arrive Sept 10 dept Sept 15 = 5 nights. I can then use this 5 to multiply out costs.
I have attached an example of what I am trying to achieve.
I have searched the forum and saw similar questions but I couldnt apply them.
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.
what i would like to do is change the fill colour of D68 if the word Air appears with in D5:D65?
View 6 Replies View RelatedI 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.
I have a range of cells that I want to sum based on a range inputed by the user. The range begins with cell c27 and ends with cell au27. I want the user to be asked the beginning cell and ending cell of the range they want to sum and then output the answer.
View 4 Replies View RelatedSo I'm creating a UserForm that takes user input to create a worksheet based on the values input. My question is: How to select a range of cells based on a variable input value? For example, it asks how many competitors there are, and the user can input 5, 8, 10, etc. So I want that many cells in Row 1 selected, whatever the value input is.
View 9 Replies View RelatedI am using Excel 2007.
Sheet 1 - I have columns filled with dates (weekdays only) For example 28-May, 29-May, 30-May up to 28-Nov. Above 10 rows below each date columns and each cell has some values.
28-May29-May30-May
0.50.50.5
1.01.01.0
1.01.01.0
1.01.01.0
1.01.01.0
1.01.01.0
1.01.01.0
Sheet 2 - I have Start date defined in B1 Cell, and End date defined in B2 Cell
I need defining the formula to sum all the cell values fall between the date range defined in Sheet 2 B1 and B2.
For Example if B1=28-May B2=30-May, I want the sum of all the numbers come under the range of 28-May to 30-May, with this above example, it should be 19.5.
Rather than having to give it a range, I'd like to have a generic range giving me room to add or subtract values in the column without changing this statement.
Code:
Worksheets("Sheet1").Shapes("Combobox2").ControlFormat.ListFillRange = _
"O2:O14"
I want the user of my excel sheet to define a certain area For example range 1:
In cell A1 he enters = 5
In cell B1 he enters 10
Beginning with C1 excel should colour the cells C1, D1,...,H1, because the area has the lenght of 6. Colour could be yellow.
For range 2:
The same in the cells A2, B2, ...
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.
I have a spreadsheet where I am adding up figures in columns.
I need to differentiate between 4 different "types" of cells to sum.
Cells with figures in them are either green, red or have no fill; and some cells contain no figures at all.
At the bottom of the column, I need to total up all the figures in red cells, as well as all the figures in green cells.
The shading of these cells is not permanent - colours are changed as work progresses, so I need the totals to keep up with this.
If it cannot be done based on cell fill colour, is there any other way to do it, other than the usual long-winded way of @sum(..... etc
Run-Time error '1004':
Method 'Range' of object'_worksheet' failed
The Function basically takes any cell in range with a formula and has it blue. If the formula is overwritten by the user it turns the cells colour Red.
Private Sub Worksheet_Change(ByVal Target As Range)
'change Colour of cells from blue if formula based to red if data typed by user
If Not Intersect(Target, Range("AS63,BA5:BP66,BT7:CI55,BU60:BU64,BX60:BX64,CA60:CA64,CD60:CD64,BT55:CI66,BT59:CI59,CF7:CF55,CF65:CF66,DJ19:DJ21,DJ24,DL5:DM36,DJ41,DJ45,DJ48,DL41:DM48,DH50:DH51,DJ50:DJ51,DL50:DM53,DH63,DJ63,DL55:DM58,DL60:DM66,DU5:DV33,DU37:DV58,DZ8:EB8,ED5:EE27,ED31:EE66,EM5:EN12,EM16:EN29,EM33:EN38,DH63,AL5:AM26,AL30:AM49,AL53:AM66,AV5:AW16,AV20:AW29,AV33:AW53,AV55:AW63,CO5:CO66,CQ5:CR66,CY5:CY66,DA5:DB66,DJ5:DJ7,DJ14:DJ15,DJ17")) Is Nothing Then
If Target.HasFormula Then
Target.Font.ColorIndex = 11
Else
Target.Font.ColorIndex = 3
End If
End If
End Sub
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 RelatedI 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.
I have built conditional formatting into some cells on ' Sheet 1' and would like a message box (saying "Check errors before closing") to appear when the user tries to close the workbook if any of these cells are coloured red (ColourIndex 3).
View 3 Replies View RelatedI have a worksheey of data and i would like the tab to be a certain colour dependant on the the contents of a range of cells within that worksheet is that possible. i know i need to use VBA and it cna be done based ont he contents of one cell.
So for example i have a sheet of people i am awaiting payment from, based on the last column " bill paid" i want the tab to be Red if there are any blanks in this column/ range and go green when all the cells in the column/ range are filled with "y"
Is it possible to search Excel by cell colour, i.e. font colour...and then change the said colour to another one?
View 3 Replies View RelatedI 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 need some VBA code to hide columns if they are outside of a specified date range.
- the worksheet i need to run this VBA on is named 'Summary'
- Columns A to G need to remain un-hidden at all times
- from H17 to ZZ17 i have every month of the relevant years listed (all there chronologically)
- D3 holds the 'Date From' variable
- D4 holds the 'Date To' variable
I need to input values rangeing from 0 to 5. When inputing I want to make the background colour change to a differant colour for each value. I can do it but only for data already in the spreadsheet.
View 9 Replies View RelatedI am working on a spreadsheet for work, and have managed to do everything I need to so far but I need to colour specific letter strings, certain colours within a range of cells (each letter string will only appear once on each sheet)
The strings I will be looking for vary depending on data entered so I will need to cell reference them
The strings that need colouring are in cells with other strings that must stay black (They cannot be separated from other strings due to the nature of the grid)
I need some strings red, some green, and some blue.
These changes should also apply to the whole workbook not just one sheet.
Is there a way to do this with the VBA code.
i want a cell to change to the colour green if 5 other cells on the page are green colour.
I want a cell to change colour to red if there are 1 or more red cells on the page.
How do I make this work?
I have a cell B10 which contains a formula.
I would like a macro which copies this formula across the same row.
However, the macro should prompt the user to select a range.
Based on the range selected and in particular the columns in this selected range, I would like the macro to copy the formula only for the columns specified in the selected range.
i am trying to merge cells based on the input from another sheet. these input will change from month to month and i need the sheet to cancel the merge and merge again based on the new input.
Example.xlsx the file showing what i need it to do.
I am trying to create an advanced spreadsheet in excel that caters for people who really need to be told exactly what to do and what not to do. So based on the selection in column A I want to disable and change the background colour of some cells as follows (but only disable the cells on the same row):
columnA = Number then disable cells D, F, G and change to red
columnA = Link then disable cells E, F, G and change to red
columnA = Image then disable cells D, E and change to red
I have this code going for a sheet i've been working on, but thers only one thing I can't figure out how to do.
I want it to take the text that was entered in the "Grid Date" input box and stick it in the bottom 10 cells (plus one space). IE - if A14 is the last used cell, paste it into A16 through A26.
I want to create a macro which asks some information like functions do. For example, I would like the macro to ask for some data to be filled by the user. For that purpose I thought about using the inputBox vba function. I wanna fill the field in the inputBox with a reference to a range from an excel sheet. So, I click on a cell and then I try to hold ctrl + down cursor to get all the range starting from that cell on until the last consecutive filled cell on the sheet. Nonetheless, the problem I have is that when I do ctrl+down cursor, I just do not get the reference I want, because it seems that the ctrl+down cursor combination does not work in the inputbox for filling a reference to a range of cells. Does anyone know some workaround to achieve this?
View 9 Replies View RelatedI have a workbook with 2 - worksheets "DATA" and "LETTER".
The "DATA" sheet has columns "First Name" "Last Name" "Address"...etc.
The user can enter in either a "X" or a "x" in cells B6:B100 on the "DATA" worksheet.
After the user enters a X or x and hits the macro button I need it to copy certain cells from "DATA" worksheet to "LETTER" worksheet. I have listed a example below.
In this example if a "X" or "x" is entered into B6 on "DATA" worksheet and the macro button is hit, then: ....
