This code checks file's date and depending on the date of files creation it has to color cell and show date (if the file is older than today) or time (if the file has been created today).
Generally this code works fine except one thing, line "Range("G14").Offset(j, i).NumberFormat = "mm/dd"" and Range("G14").Offset(j, i).NumberFormat = "hh:mm"" work every second run of the code.
Meaning, I have to run this code two times on exactly the same set of files. When files change (for example new have just been saved), first run changes color of the cell but doesn't change the format. Second attempt works fine.
I tried moving these lines at the end of all formatting lines and even repeat lines with NumberFormat again but no change.
I am trying to insert data from an excel sheet to a sql database. Everything works okay but the Time value. In the Excel sheet it is '12:09 PM' and when it is read with 'cl. Offset(0, 6).Value' it turns it into '0.50625'. Is there a way to format it so it can be inserted as a datetime?
Dim cl As Range For Each cl In Range("A1:A12") stSql = "INSERT INTO GasData (Delivery,OpenPrice,High,Low,Last,Change,Time) VALUES ('" & _ cl.Value & "','" & _ cl.Offset(0, 1).Value & "','" & _ cl.Offset(0, 2).Value & "','" & _ cl.Offset(0, 3).Value & "','" & _ cl.Offset(0, 4).Value & "','" & _ cl.Offset(0, 5).Value & "','" & _ cl.Offset(0, 6).Value & "')" cn.Execute stSql Next
In the sample i have list of codes,also i have picture according this codes: Can i somehow conected pictures and codes? My pictures are in jpg form,can i insert them into cell,where picture should has form as cell has.
When the code from the Data sheet in cell A2 sheet 1,than show the picture conected with this code in Data sheet.
I would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?
Column B has list of Balance Sheet items for business and Column D has associated values. I need to perform the following:
Look for following text and if it finds it then change the font size to 16, bold it,center align and make the associated cell in column D as mentioned below.
ASSETS ....change the cell color in column D to white (Invisible). Cash and Bank Accounts TOTAL Cash and Bank Accounts Other Assets ....change the cell color in column D to white (Invisible). TOTAL Other Assets TOTAL ASSETS
I m creating a sheet in excel where I need to do some conditional formatting but need more than 3 rules so i'm guessing i'll need to use some vb code.
Basically I have a range of cells say from c12 to ag15 These cells all have formulas in linking to other cells in other sheets.
Tha values that will be in them are as follows: s, h, hd,ooo,z and maybe 1 or 2 more will be added later. I need the cells background and text colour both to change to a colour depending on the value in the cell.
eg. cell value = s then background and text colour both to be yellow.
I'm looking for a code that automatically changes the format of any pasted in data to Match destination formatting. Not getting the results I want from protecting the sheet.
I keep getting the "Else without If" Error and not sure how this code works.
Code: For Each oBookMark In oDoc.Bookmarks Set objX = wsData.Rows(1).Find(oBookMark.Name, LookIn:=xlValues, lookat:=xlWhole) If Not objX Is Nothing Then Select Case oBookMark Case "NetToServicer", "PurchasePrice", "MaxCommission", "JrLien", _ "HOALiens", "Repairs", "Termite", "RelocationAmount", "CashContribution", _
I currently use some Excel VBA to copy graphs from different sheets in an Excel workbook to a new Powerpoint presentation (one graph per slide). The graphs in Excel are simple line graphs with weekly dates UK style (dd/mm/yyyy) on the x-axis, and values on the y-axis. All works well when these are copied and pasted into PowerPoint as pictures in their own slides, except that the dates become Americanised (mm/dd/yyyy). I need to write in the VBA code to preserve the original date formatting? I am unable to attach the Excel and PowerPoint files - I hope the explanation above is sufficient.
Can Attached code be changed to alter font colour,type. rather than just fill colour.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer If Not Intersect(Target, Range("e:Az")) Is Nothing Then Select Case Target Case "Ph" icolor = 6 Case "Li" icolor = 12 Case "O" icolor = 7 Case "An" icolor = 53
Was hoping to get some help with a mini project of mine.. Currently I am looking to create a progress/commission report (pull data from an access dbase) and populating an excel sheet. I can manage to pull in the data fine its just gettting into a format that is the complicated thing. I was hoping that someone might have a few moment to pick thru my code (see attached) and maybe suggest an alternative/more efficent way to do the refreshing.
I need in changing the format of sheet2 to exactly like the following image below. *there are some codes in sheet2 which I think is the place to change the format.
I have a macro which is designed to collect information and then assign each piece of information to a defined cell. I have Dim statements for each piece of information i.e.
Dim Info1 = Surname Dim Info2 = Initials And so on. Dim Info11 = Date of entry
This is working perfectly but the date when entered is reversing to american format. 08/05/2006 becomes 05/08/2006. Type into the cell manually and it works perfectly let the macro put the date in and it changes format. Can anyone supply a line of code that would format the date to dd/mm/yyyy? Then I could insert it after the part of my code which basically says go to this cell and put in the date.
I'm try to get a macro to do a 5 Colour Conditional format. I've searched the previous questions as recommended, and found code that I think will do the job. I've tried to modify it to my own specs, but I can't get it to do want I want. Basically I need it to colour code the cells based on the ball number within them. As I can't get the code to work, I don't know if the colour values are correct? Also given that cells are white by default, balls 1 - 9 don't need to be part of the code do they? I've attached a small example of what I've managed so far, with the macro code enbedded. Also what's the correct way of cpoying & pasting macro code from one spreadshhet to another? The simple copy and paste seems to stop the code working correctly. Any help would be greatfully received.
I have a column in excel which gets populated in the format "2/1/1980" when copied from a different source. I want a macro to run on this column and convert this in the below format. Required format: '02-01-80 (note the ' symbol before 02 which will keep it as text)
I need this macro to run on two columns and replace them with the required format. I can do a find and replace either in the sheet or through hard coding the VBA, but as the date format keeps growing, I would need to do this via code and hence requesting the same.
I can't find a custom format to validate a properly formatted Canadian postal code on entry, so I think my last hope is to ask someone to write me a VBA code.
Canadian postal codes consist of six characters with a space in the middle: a capital letter, a number, a capital letter, a space, a number, a capital letter, and a number.
Therefore, M2A 3J4 is a properly formated postal code.
I want a code to fix an improperly fomatted postal code (such as M2A3J4 or m2a3j4 or m3a 2j4) on entry.
I have this code that copy/pastes a range from another sheet for every nth row. In the code I have pasted cells I need to merge the cell with the cell to the right and format it. I thought I was doing ok but the code keeps returning an error (run-time 1004) and I don't know the solution, it is beyond my level of VBA.
Code: Sub test() Dim ws1 As Worksheet Dim ws2 As Worksheet Dim lngRowCounter As Long Dim newRowCounter As Long
[Code] ...
The code in red is the problem and I have commented it out so it can run, but I need it to merge and format. I don't know how to address range/cells in column B and C together (they are the two cells I wish to merge and format).
I am trying to creat a code that with take the value of an active cell and depending on this value will assign a backcolour to a label corresponding to the cell. so far so good. I then want the code to offset to the next cell in the range read its value and assign a colour to that cells backcolor.
here is an idea.
range("A1").select for n = 1 to 4 if activecell = "A" then Label1.BackColor = RGB(0, 0, 0) else if activecell = "B" then Label1.BackColor = RGB(0, 0, 255) else if activecell = "C" then Label1.BackColor = RGB(0, 255, 0) end if activecell.offset(0,1).select next n
Firstly I would like the next loop (refering to A2 in this example) to refer to Label2 not Label1 and so on. secondly the example would loop through 4 cells in one row (A1:A4) but I would like the code to apply to several rows ie (A1:D4).
so thats 16 cell and 16 labels. I could code this in a very inefficient way but I am sure ther is a simple method.
Is it possible to get a inputbox to validate an entry as time in the format of HH:MM ie: user must enter a valid time in the above format before the macro will proceed to the next line of code
I found code that I would like to use conditional formatting code as shown here. Here is the code just in case:
Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Select Case Target Case 1 To 5 icolor = 6 Case 6 To 10 icolor = 12 Case 11 To 15 icolor = 7 Case 16 To 20
how can I get this code to work across the entire workbook and not just for the specific worksheet? I tried pasting the code into the This Workbook under VBA Project and changing the Private to Public but no go.
I am experiencing odd behavior with Excel (v. 14). When a spreadsheet is opened, format setting of almost all cells in the workbook have changed to a custom code that reads "[$-409]0%". AFAIK the first part is not even a valid tag.
Because the underlying data is not changed, I can just select cells and reset formatting to "Number" or "General" and everything's fine. However, I'd like to get to to the root of the problem.
The problem occurs with only some workbooks. I should mention that the workbooks are circulated in different language versions of Excel, which may contribute to the problem.
I have VBA code the extracts data from a source and puts it into excel. I need to change the format of some of the extracted data so i can use it for a VLookup formula. Reference the attached example. Columns A AND B have the extraced data which are dates. In Columns C and D or E and F I have typed what I would like the data to look like so I can use in a Vloolup formula. Is there VBA or a formula that word change the format of the dates in Columns A and B to look like C and D or E and F. The changed format could be moved to any new cells or rows.
I'm trying to search for a column by looking for a specific column header and then format the entire column in the specific number format desired, but I keep getting an error message with this line.
Code: With Rows("1:1") .Find(what:="Go Live Date").Column.NumberFormat = "m/d/yyyy" End With
The following block of code is evaluating the term in the array, if it exists and the value in col. M is a non integer, (which what I am testing for are fractions) then format the cell to a fraction number format. That is ok, except it is changing the numberformat on text terms. I want it only to change the numberformat on numeric values. I added the function Isnumeric to this line:
Isnumeric(cells(i, "M").value) Int(cells(i, "M").value) which I thought would only evaluate numeric cells only, but this was unsuccessful.
Original Code: Unedited.
For i = 4 To LRowf For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTG", "BOOTY", "HWRISR", "HWBLTS") On Error Resume Next If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) And _ Cells(i, "M").Value Int(Cells(i, "M").Value) Then Cells(i, "M").NumberFormat = "# ?/?" On Error GoTo 0 Exit For 'End If End If
I have created a userform but just unsure how to format the code in order to get information to appear in a spreadsheet http://www.srfl.ca/userform.htm
I want to send the value of selected item in texbox , the value format is change by combobox value ..and I want the value is fill on the next column of sheet ( see attached file please)
