if I have one cell that ive enlarged to half the sheet.... and it is effected with rollover hyperlink so that when I hover over other cells. it turns different colours..
is it possible to make that cell open up different files using the double click event depending on the colour it currently is?
i.e. if its blue, open file1. if its yellow, open file 2 etc
A column of cells can be 1o different shades of green according to their value (achieved by using conditional formatting.) I want to arrange that at a certain time all the cells of a part of the row of a formatted cell are the same colour.
View 4 Replies View RelatedI found the code below to do almost exactly what i want. However, i would like to know if it can be modified to only affect a specific cell range. In my case, the cell range is B9:AF37.
Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range
On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error Goto 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
i'm having a problem with a script I have (script is below). What i'm doing is running a script that displays files in a certain folder, those files are then displayed on sheet1 column O and are linked so when they are clicked upon, the file will open. I also have a combo box with a input range of $O$O. That puts the values in column O in the combo box. Now here is a problem and request.
Problem first, when I select the file names from the combo box, they don't open the file, all it does is display the file name in combo box and that's it. how to I get it to open up the file? Now for a request, I have another sheet that is almost exactly as the one i described above. The only thing different I want is when someone clicks on a file name from the combo box, instead of it automatically linking to the file, I want them to click on a button to display the file.
Sub HyperlinkXLSFiles()
ActiveSheet.Unprotect
Dim lCount As Long
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "c:Sec"
.FileType = msoFileTypeExcelWorkbooks
' .Filename = " Book*.xls"..................
Is there any way to have a cell change its colour depending on whether a condition is met. E.G., can i have it as green if the result is a plus number and red if its a negative?
View 2 Replies View RelatedHow to a change a cell colour to say red in B6 if cell b12 = 1 and if e6 = 1 to change to green. I thought I might be able to use conditional formatiing but no. I had set the spread sheet to do a cell just for a condition representing a sum from another worksheet and it was working fine but i have to incorporate the two together and am stock.
I have attached a spread sheet.
i need to change the colour of 4 cells depending on the one cell.
i have found some vba codes on the net but they are all based on numbers and i need it on text.
so if a1 ="alert" make A1 to A4 red
if a1="passed" make a1 to a4 green... and so on
i cant used conitional formatting as i need more than 3 colours
I would like to conditional format an Object depending on the variance between 2 weeks.
If the value is > than 0% the arrow is Green and points upwards.
If the value is = to 0% the arrow is Yellow and points at at 90 degree to the left.
If the value is < than 0% the arrow is Red and points downwards.
Attached is a sample : rrow Conditional Format.xlsm
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
Is 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 RelatedI have two dates lets call them cells A1 and A2. I then have another cell that caluclates the difference in these dates into a simple number (ie not another date) - A3. I need one of two things.
Ideally I want the whole cell (A3) to appear as a Green (fill) cell if the value is below 5, appear Orange if beween 6 and 9 and appear as red if its 10 or over.
I do not know if you can colour a cell that has a formula in already. So what follows is my current 'work around', which, if you can not colour the A3 cell, then I would need this cell coloured (A4). =IF(D12>7,"Urgent Attention",IF(D12<5,"New",""))
I have a template I have been asked to amend and I know I need to use Conditional Formatting I just can't crack the formula I need to use. Cell A2 will have either "CD" or "CW" or "IN" input into it
When user inputs either "CD" or "CW" into cell A2, I want cell G2 to have "Margin Movement" automatically displayed. When user inputs "IN" into cell A2, I want cell F2 to have "MV" automatically displayed, and I want cell G2 to be coloured in bright red fill. Then user is to go to cell G2 and input some text as a description, and I want the red fill to disappear once they have input something in there.
I thought I had it licked using IF statements for the "Margin Movement" and the "MV" cells, but I can't get the conditional formatting to work - I assume this is because the cells aren't technically "blank", as they have IF statements in them.
I am doing a spread sheet for to enter the times that i do in a rally.
So if i post a time of 00:49.3 and the target time is 03:54.3 i want the cell to turn red, or if the set time is less then the target time it should be green.
How can i do this in excel
Afternoon everyone i am having abit of trouble working on an excel 2007 spreadsheet. In cell I1 i have a tab called Color. i want the cells below I1 to be filled with one of 3 colors green, yellow, or red depending on whats in cell F "Status" (closed or open - in progress) and cell G ECD for estimated completion date.
Green - i need it to fill green if status is closed. Yellow - need it to fill yellow if ECD is any date greater than today and if status is open. Red - need it to fill red if ECD is todays date or older and status is open.
how to make a cell a color depending on a number in a different cell? Example...if A2 is 92.6 how would I make D2 blue? But if A2 were 91.9 how would I make D2 red?
View 5 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 an excel file which contains data, I want to be able to search this file and to fill the cell and its next 3 cells to the right with a certain colour. Is this possible as I have wrote some vba to do this but, Im only able to search and not fill the cells with the correct colour.
way to highlight selected cells if one of the selected cells equals a certain value eg
A B C D E
1 X X X Y X
2 X X X X X
3 X X X Y X
I select cells A1-E3 and if column D = "Y" then colour fill the row from A to E
As seen from my attached file,
The colour of the percentage cell changes according to its percentage. I would like the S/N cells to have the same colour as the total percentage cells automatically. (Even when the percentage updates)
Secondly, for the cells under "Target", if the target dates are 1 day before today(the current date on a particular day) and the actual date is not filled, the cell fill will turn amber.
If today is on or after the target date and the actual date is not filled, the cell will turn red.
However, if the actual date is filled, the target date cell will be filled green, overwriting the above two condition.
FormattingHelpExample.xls
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'm trying to ask my spreadsheet to fill a cell with either 'YES' or 'NO' depending on the value of one cell. I've succeeded in getting it to enter 'YES' but can't figure out how to tell it to choose between the two options. This is the formula so far
=IF(L5>2,"YES")
in my spreadsheet (attached), i want it so that when someone clicks a button, it will fill in the next available cell in column A. For example, when I click on the "Create" button on the green NIPRNet side, then it'll fill in the next available cell in column A (cell A4) with "(N) Created Account". Then, if I click on "Unlock" on the red SIPRNet Side, then it will fill in cell A5 with "(S) Unlocked Account". and so on, and so on, down column A.
At the moment, i have it so that all the cells in column A are dropdown menus. But I want to eliminate the drop-down menus and use buttons instead.
it is possible to change the colour of a cell text and / or cell background depending on a value.
For example I have currently 2 cells with numbers in
G3 = 4.38
K3 = 8.68
What I wolud like to happen is for the cell with the lowest value to change to Red.
The Column letters will always remain the same but I do plan to add a lot more rows.
I am currently trying to adapt the following code which is in VBA (Sheet1). It currently ensures mandatory cell entry of cell C2 before saving, and if this is not done then it would not save the item into the relevant directory. I would like to adapt this code so that cells B2 to R2 are all mandatory, and if they are not filled in a message box with the cell title (which would be in B1 to R1) would pop up. If the cells are not filled in then it will not save (the same function it carries out for cell c2 currently). The code is attached to a command button, i have tried to make the additional cells mandatory by playing around with the If Trim(.cells(2, 3).value) part of the code with the AND function etc however i have had not had any luck.
View 7 Replies View RelatedI need a macro that will change the background colour of a cell depending on whether it equals a certain variant. Let me explain.
Cells D8 to N8 will either equal 1, 2, 3 or 4. Depending on the value of the cells on row 8 (D8:N8) I need the above cells on row 4 (D4:N4) to have a different background colour.
If the cell on row 8 is 1, the cell on row 4 must have a green background.
If the cell on row 8 is 2, the cell on row 4 must have a yellow background.
If the cell on row 8 is 3, the cell on row 4 must have a red background.
If the cell on row 8 is 4, the cell on row 4 must have a blue background.
Therefore, if D8 = 3, D4 will have a red background. If F8 = 2, F4 will have a yellow background. And so on.
I need a macro that when ran changes the background colour of the cell range D4:N4 depeding on the value of the cell 4 rows beneath (D8:N8).
i am looking for a formula, if i put it as plainly as i can then i hope someone can work it out for me!
if ad4 is greater or less than 30 then ad4 text is red, if ad4 is equal too 30 then text is green
I have 22 CSV files. I want to search each of those files for a specific text string (ie: "profile" or "description"). When the macro finds the word in any one of the CSV files I want it to copy that complete csv worksheet and paste it into an excel xls file.
how I can go about doing this. Not also that while vlookup is possible, its very time consuming if i have to do this 50 times over and each time the csv files have different file names. that is why i want to search within a file (each file has its own unique identifying text string)
I have a spreadsheet with several freeforms. I would like them to change backgroundcolour, if the content in a cell is equal to another cell.
Basically I would like my shape "Freeform1" to change backgroundcolour to RGB (0, 180, 0) if Sheet1.Range ("A1") = Sheet2.Range("D3").
If it's not the same content in both cells, I would like the freeform to remain unchanged, that's RGB (79, 129, 189).
This should happend automaticly, so I don't have to click the shape to make it happen.
I am trying to create a macro that will open certain files based on the contents of a cell. There are three possible files that I will want to open.
File1.xlsx
File2.xlsx
File3.xlsx
If the contents of cell Q2 = 10, 20, 30, 40 or 50 then open "File1.xlsx"
If the contents of cell Q2 = 60, 70 or 80 then open "File2.xlsx"
If the contents od cell Q2 = 90, 100, or 110 then open "File3.xlsx"
If the contents of Q2 do not equal any of the possibilities listed then I would like an error box to show with the option to end or debug the code.