I have a column for each month where the dates are entered on rows. Is it possible I can color a cell if the date entered in a cell for that month is 15 days past the end of the month. Like, if in a January column I enter a date on one of the rows as Feb.20, then the cell fills with red colour. I tried doing it using conditional formatting, but don't know how to say 15 days past end of the month?
View 5 Replieshow I might be able to colour certain cells in a row a specific colour when a specific value is input.
For instance:
The value "A" is put into row A1. A1 and C1 cell colours change to green
The value "B" is put into row A2. A2 and C2 cell colours change to orange
etc. (not sure how many colours yet)
I sort of have a script set up, but there are certain things I dont know how to do. Like target the specific cells that need colouring.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 0 Then Exit Sub
Select Case Target.Value
Case "A"
Target.Interior.ColorIndex = 1
Case "B"
Target.Interior.ColorIndex = 2
Case "C"
Target.Interior.ColorIndex = 3
Case Else
Target.Interior.ColorIndex = 0
End Select
End Sub
I am currently using a piece of code (within a bigger piece) that colours the dates in two columns based on how close it is to the current date. It works fine but sometimes the sheet it is looking in has over 3500 rows so it is taking for ever (well over 6 mins)
The code is
HTML Code:
Sub ColDate() 'date case opened
application.ScreenUpdating = False
Dim MyRg As Range
Dim F As Range
Dim DateDiff As Long
Set MyRg = Range("g1:h" & Range("H" & Rows.Count).End(xlUp).Row)
For Each F In MyRg
[code]...
I am trying to get a row of cells to highlight a percentage based on a date range
Below is an example of what my spreadsheet will look like, very simple for managers to read and understand but I am stuck on how i can get this to display the right way.
In the example i would need the Jan column to colour for a certain percent for 21 days and continue to feb for 26 days. Im not sure if this makes sense but this is what they are asking for. Colour bars to simple show the percent of days off each month.
Name Start Date End Date Jan Feb Mar
Dale 11/01/14 26/02/14 21 days 26 days
I have attached the spreadsheet for an example : Book1.xlsx
Is it possible to get the following to work.
If cell A2 has the number 5 in it (as a result of a calculation in that cell), is it possible to get the next five cells in that cell to colour themselves in?
Obviously, as and when the value in cell A2 changes, i need the number of cells coloured in to change as well.
In addition to this, the rows that this will happen in will vary week to week so i'm not sure if VBA is the way to do this.
Also would there be any problem with negative figures as when that happens i don't want any cells to colour in at all.
How do i have colouring to a reference cell what i mean is that on certain formula i get BUY or Sell on that cell. As per the conditional formatting i have made the colour change BLUE FOR "BUY" & RED FOR "SELL"
On second sheet i link the above TEXT., but the COLOUR OF THE TEXT Does not come.
Does CONDITIONAL Formatting come with reference cell.
I've been trying to get a macro together which would colour all the tabs in my workbook based on whether or not a certain value exists in a certain cell. In this case the cell is C7 and the word there is "elective class:".
View 7 Replies View RelatedAs you can see from below I already have a spreadsheet which updates the fill colour of cells based on a certain criteria. This was initially set up in Excel 2003....I have now moved onto 2010 and want to use a certain colour based on it's RGB value. I tried as you can see from below, the part which is commented out (as it didn't work)
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Long, lngIndex As Long
For n = 6 To 842
[Code].....
I have 6 Headings in excel named...
"A" in cell A1, B in B1, "C" in C1, "D" in D1, "E" in E1 and "F" in F1.
There are two projects.
Project 1 has phase A, D & F and Project 2 has phase A, B, C, D & E.
My Specification follows...
1). Take Prject 1 - Which starts from A...in cell A2 I will keyin "A". When the phase comes to an end I will key in the end date of the phase. As soon as I key in the end date in cell A2 Letter D should automatically appear in the cell D2 and when Phase D comes to an end I will key in the end date in Cell D2 which should automatically keyin F in the cell F2. and is the same case for Project 2.
I would like to know how to use a VBA code to insert today's date into a specific cell (B9) when any cell in column B (B2:B8) has changed.
View 3 Replies View RelatedIf a cell has a date in it (date payment received) I would like to replace it with the value of the payment received that is given in a different cell - how do I do it ?
View 2 Replies View RelatedWhat I have done is create a macro in Excel and then opened up the VBA editor to make a lot of changes to the initial macro. I am reformatting a whole lot of separate spreadsheets over many workbooks.
Here is the code that is generating the error (Run-Time error '438', Object doesn't support this property or method):
Code:
Range("E2").Select
Selection.Format = "long date"
With Selection
.Font.Size = 11
' .Format = LongDate
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
As you can see, I first tried the ".Format = LongDate" and did put LongDate in "" which did not work, then I tried the "Selection.Format = ..." after the "Range" statement, and that did not work either. So you will actually know, I am trying to set the format for the cell "E2" to a Long Date format.
I have a problem regarding highlighting a specific cell, when i entered a specific date in another cell
Let me explain
If there is a Name, joining date, termination date/resignation date in cell A1 A2 & A3 respectively.
If the name is John, Joining date = 01/01/2012, Resignation Date 06/06/13
I need a formula which highlights the cell A2 plus all those cells which have the same date when i entered a date (i.e 01/01/2012) in another cell.
I have this formula counting down days until my next fire drill:
[Code].....
However... Every time the drill is done I need to change the date in the formula for the countdown. That I can have it reference a cell instead?
My initial idea was a formula like this:
[Code] .....
(however this code doesn't actually work ...).
I need the code to add the selected date to a specific cell lets say cell D8
at the moment it adds the date in any cell selected
I am trying to date stamp a cell when another cell has been changed from a drop down selection list. For instance: If cell A1 is changed, time stamp B1. I have tried both the following codes and they both work for 1 column.
"Private Sub Worksheet_Change(ByVal target As Range)
If target.column = 1 Then Cells(target.Row, 2) = Now()
End Sub"
[Code]....
The problem is that I want to time stamp cells in columns FHJLN, when the column to the left has been modified (EGIKM). So I basically want to repeat the time stamp code for multiple columns. I have tried messing around and can get not more than 2 columns to work at a time.
Basically, it is a scheduling of approving or rejecting of request dates. whereby, i will receive the request dates from Google doc form and then i will extract the data from web into the excel. The data i receive will include the ID, Date request and Remarks.
With the Information in hand, the data will then be "plot" into a schedule (which is a year calender that i have created on excel) base on the ID requested from the web and the ID in the excel.
After plotting, i also want to check if the number requested date exceed the quota set PER DAY, if it exceed, then i have to reject, if its not then approve.
So here comes the problem.. After all the dates have reflected on the calender, I want to get the information from the calender result(which is whether the date is approve or reject) back on to respective ID and Dates requested.
I have attached a example excel for clearer view of my explanation.
I have a table that has a number of new starters and corresponding appointments offered, what I originally required was to lookup the chronological date after the new start date.
However this has now been scuppered by my boss who has requested that not only lookup the date, but also add who the appointment is with, but if I do this the first array formula stops working and to tell the trust I'm not to sure how to do it anyway.
i want to insert the last date written in a cell into a specific cell automatically,
1- Date column is D containing dates from starting of the month
2- A1 is cell in which last date from column D should automatically be inserted.
automatically copy the last entered date in column D and pastes it to cell A1,
I have created a user form using the addins that come with excel to create a calendar that will allow the user to select a date and automatically put that in a specific cell. My system is office 2000.
The code is as follows:
Private Sub OK_Click()
Dim i As Integer
Dim myCell As Range
i = 0
For Each myCell In Selection
myCell.Value = Calendar1.Value + i
i = i + 1
Next myCell
Unload Me
End Sub
Now, I gave it to my boss who has xp, and I get the following VB error when she chooses a date and clicks on the ok button:
Method 'Value' of object 'ICalendar' Failed
formula that can automatically use the current month date to extract information from other cells on my spreadsheet.
For example: This formula should be located in cell C6. Cells D6, F6, H6, J6, L6, N6, P6, R6, T6, V6, X6, and Z6 (these cells correspond to the months of the year, January to December respectively) contain the values I need. The month names are labeled above in row five.
Let's say the current month is March, I need the formula in C6 to automatically know that it is the month of March and to pull the information from cell H6 (which is the March cell). Then when April comes along, it will know that it is April and to only pull the information from cell J6; and so on until the end of the year.
So, this works perfectly by itself: {= SUM(IF(MONTH(I4:I17)=1,G4:G17,0))}
And this works perfectly by itself: {=SUM(IF(YEAR(I4:I17)=2007,G4:G17,0))}
But this doesn't work at all: {=SUM(IF(AND(MONTH(I4:I17)=1,YEAR(I4:I17)=2007),G4:G17,0))}
SUM by both a specific month, and a specific year from a single date field?
excel.jpg
how to make my network days stop counting when I insert a value in the completed column (in this case H3). I have a Date Received column (A3), a due day column (G3) and the last column with the date (N3). My remaining days column keeps counting after I filled the completed cell (H3) and won't stop counting after. I need a formula to stop after the completed cell is fill up.
I have a large excel file with the top seven rows such as you see in the attached file. In row five there is a bunch of info before the month and the year. What I want to do is get rid of that info besides the month and year. I than want to sort each column by the month and year so that all the columns are in sequential order.
demo.xlsx
I have a spreadsheet that contains data I import from various sources. Once the import is complete I need to colour various cells based on their contents. Normally I'd use conditional formatting, but there are more than 3 conditions so I need to use VBA (preferrably that i can turn into a macro and assign to a button to click once I've finished the imports each time)
eg: Everytime a cell content is the word "RED" , colour it red - then the same with various other colours.
Google has found me a change event piece of code but it doesnt work when you're cutting and pasting some info.
I have a rather large database that I'm trying to automate colours in, based on data that is inserted into Column 'D'. I've used the following code (with some success), but it colours columns that I don't want to colour:
VB:
Sub KeyCellsChanged()
Dim Cell As Object
For Each Cell In Range("D1:D5000")
[Code].....
That works fine - but the problem I have is that I only want to colour columns A:N, Q, T, V, AB:AE, etc. (random columns and not always together). I have already tried to replace parts (as follows), but get all sorts of errors (in particular Run-time error 1004):
VB:
If Cell = "Rabbit" Then
Cell.Range("A:N,Q, T, V, AB:AE").Interior.ColorIndex = 42
Is there someway that a line of code can determin which cells to colour in the row, or alternatively code that will colour the columns a specific colour and make them stay that colour when the first code above is used?
Just trying to work out the best way to be able to determine autoshape colours using cell values. For example I have 12 shapes that need to coloured either Red, Green or Orange depending on a specific cells value, 1 for Red, 2 for Green and 3 for Orange.
I have sort of managed to successfully complete this for 1 of the shapes, by using the RGB Fill option (Using 3 = Red, 4 = Green and 45 = Orange). But cannot for the life of me work out how to do this for multiple shapes using different cell triggers. Below is the code that I currently have and the spreadsheet that is linked to:
I have a gone through quite a few topics in this forum.....but couldnt find answer to my question....may be because its too basic. I have a worksheet with 10 columns. I am trying fill colour in the rows that have "text" in column J (in other words.....their column J is not blank). I figured out how to fill the colour in the cells in column J when they are empty, but cant figure out how to colour the entire row from A:J.
View 4 Replies View RelatedI'm trying to automatically shade a number of cells dependant on the value in a single cell. ie. If D2="quote" then I want A2:Q2 to be yellow. If D2="Design" then I want A2:Q2 to be green etc etc. I have about 6 options in all of what D2 could be.
I then want to repeat this for about 200 rows.
I currently have some code which reads a stock file, adding up the stock for each individual item (which may be in one or more locations in the warehouse) and then placing the total stock count in a cell with the detail (of how much stock is in each location, the location and the expiry date) and a separate line for each location, in a comment.
The comment is constructed one line at a time as the code finds the stock records, see code below:
With shtWorking.Range(strStartColumn & intRow + 2)
oldComment = .Comment.Text
.Comment.Delete
.AddComment oldComment & vbNewLine & strComment
.Comment.Shape.TextFrame.AutoSize = True
End With
All this works very nicely, but I want to colour individual lines depending on the expiry date. This can be done manually in Excel, but I cannot find a way of colouring individual lines of text in a comment using VBA.
So, does anyone know of a way I can colour individual lines of text in a comment, using VBA?