Change Cells Colour: Range Error '1004'
Jan 10, 2007
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
View 4 Replies
ADVERTISEMENT
Apr 18, 2009
I keep getting the following error : Run time error'1004'; Application-defined or object-defined error when using the following
View 8 Replies
View Related
Feb 19, 2010
I've been trying to copy values from one range to another, and the ranges need to be dynamic. Normally I use a combination of the 'range' and 'cells' properties, like this:
View 2 Replies
View Related
Dec 5, 2013
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.
View 3 Replies
View Related
Mar 9, 2007
I had to add a column to all worksheets... so edited the code so it would still refer to the correct cell. I changed the two references to "K54", to "L54"... but following this change, I get an 1004 error stating "Select method of Worksheet class failed", and the following line is hi-lighted.
ws.Select Replace:=False
Sub PrntUsedShts()
Application. ScreenUpdating = False
'set page counter
Dim PgCnt As Integer
PgCnt = 0
'select sheets to print
For Each ws In ActiveWorkbook.Sheets
If ws.Range("L54").Value > 0 Then
ws.Select
Exit For
End If
Next ws...............
View 4 Replies
View Related
Mar 24, 2014
I 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"
View 5 Replies
View Related
Jun 13, 2014
I 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.
View 3 Replies
View Related
Jan 3, 2013
The following code works fine, but when I put the code in an ActiveX Combobox Change Event it gives a run-time error 1004. ("Select Method of Range class failed")The error occurs on the following line
Code:
Worksheets("SAVED").Range("A" & l).Select
Code:
Dim l As Long
Application.ScreenUpdating = False
l = Application.WorksheetFunction.Match(Worksheets("DATA").Range("O34"), Worksheets("SAVED").Range("A1:A10000"), 0)
Worksheets("SAVED").Activate
'ROSTER===
Worksheets("SAVED").Range("A" & l).Select
Selection.Resize(1, 739).Offset(1, 2).Copy
[code]......
View 2 Replies
View Related
May 25, 2006
I encounter an problem when I migrate the excel application from excel 97 to excel 2000. The application basically composed of VB6 and VBA. When the program execute to line "ChangeHistoryDuration", i.e.
'Turn On the Audit
ExlApp. ActiveWorkbook.KeepChangeHistory = True
ExlApp.ActiveWorkbook.ChangeHistoryDuration = gi_duration
ExlApp.ActiveWorkbook. SaveAs w_filename, , gs_open_pwd, gs_write_pwd, , , xlShared
Run-time Error '1004', Method 'ChangeHistoryDuration' of object '_Workbook' faild was found. However, it runs successfully in excel 97. Does anyone know the possible problem that cause such error.
View 4 Replies
View Related
Dec 22, 2011
I am getting Run-time error 1004 "Cannot Change Part of a Merged Cell". I am trying to copy Sheet1 cell E11 (merged cells E11:G11) to Sheet2 cell E11 (merged cells E11:G11).
Sub PartBalance()
With Sheets("PART REQUEST")
If Range("D11").Value - Range("K11").Value 0 Then
[Code]....
View 3 Replies
View Related
Jan 9, 2013
I have been facing a run time error 1004 ("Cannot change part of a merge cell") when I am to run the following code. I have some merged cells in my excel sheet.
Sub Save2013()Sheets("Invoice").Range("I25:I33").ClearContents
Sheets("Invoice").Range("K25:K33").ClearContents
Sheets("Invoice").Range("K15:M19").ClearContents
Sheets("Invoice").Range("E15:G17").ClearContents
Sheets("String").Range("I58:J58").ClearContents
Sheets("Invoice").Range("T14:V1419").ClearContents
ActiveWorkbook.SaveEnd Sub
View 1 Replies
View Related
Nov 1, 2006
When I paste data in Sheet2 in the attached CF Example2 file (Just by Selecting A1:L18 Cutting it and Pasting in the same place), the action of the VBA code in the Thisworkbook is correct but I am getting the 1004 Error Message from the VBA debugger saying "Run-time Error '1004': Application-defined or object-defined error
View 2 Replies
View Related
Mar 11, 2014
I can't figure out whats wrong with this code:
[Code] ......
Its give me error 1004 on this line:
[Code] ........
Its copying the data just fine, but its having issues with pasting it. The data should be pasted into the same corresponding columns (so also starting in A Column), in the next available row (in Column C from row 2 on).
View 3 Replies
View Related
Feb 2, 2009
I am using the following macro to format a sheet - I recently added a auto-fill part to it which essentially moves a number from one column to another and autofills it in all the cells until it encounters another number of the same kind in the column it moved the previous number from.
When I try to run this macro it does everything upto the auto-fill part and then gives me error 1004 [the set cell = cell.offset(1,0) ] is highlighted in yellow.
View 4 Replies
View Related
Dec 29, 2013
The following five lines of code are inside a loop in which i goes from 1 to 600. When i was 594 a condition allowed these lines to be executed. (The last was in my original code and the others were just added to try to figure out why the last had a problem.) The first three work and the last two trigger error 1004 "Application-defined or object-defined error." All I am trying to do is to fill some cells with a dummy value. It doesn't matter whether I try to fill with 1 or with "1".
Worksheets("Volumep").Cells(5, i+3) = 1
Worksheets("Volumep").Range("vv5:vv104").FillDown
Worksheets("Volumep").Range("vv5:vv104").Value = 1
Worksheets("Volumep").Range(Cells(5, i+3), Cells(104, i+3)).FillDown
Worksheets("Volumep").Range(Cells(5, i+3), Cells(104, i+3)).Value = 1
Whether or not there is a different or better technique for filling cells, the range specification is the real issue I am trying to solve. The referencing issue produces the same error later in the module where more complicated work is being done.
Bonus question: is there a general prohibition forbidding the mixing of range("a1") and cell(1,1) styles of addressing in the same line of code? (Not a factor in the immediate problem but related to previous problems I have encountered)
View 2 Replies
View Related
Dec 26, 2008
Range(Cells(1, 1), Cells(257, 257)).Select
This gives the Run-time error '1004'
I searched these forums for any old posts to work around this but did not find any threads. If anybody knows a good thread about this, I would be greatful.
Basically I run some code to generate Startcolumn, StartRow, EndColumn, EndRow. If there is a limitation of 256, the code is basically useless. I don't know of a way to use the A1 range method whilst using the Range("A1:....")
View 9 Replies
View Related
Apr 20, 2012
Trying to find the last cell in a range. The Error msg I get is
Method 'Range' ob object '_Global' Failed
Here is my code
Code:
Sub Fleetcopy1()
Dim wbk As Workbook
Dim EndColumn As Integer
Dim EndRow As Long
Dim EndCell As range
Set wbk = ThisWorkbook
EndColumn = Cells(1, Columns.Count).End(xlToLeft).Column
EndRow = Cells(Rows.Count, 1).End(xlUp).Row
EndCell = range(EndRow, EndColumn).Address
Bolded is where the error comes up. Not sure what this means.
View 4 Replies
View Related
Jan 30, 2013
It is designed to submit data specific cells from a userform. It works fine when I select and day, 1,2,3, etc. until I get to day 27-31. For some reason I get a Range of Object Failed error 1004 every time. I don't understand what's changing to cause the issue. The red text near the bottom is the one that it tells me is the problem.
Code:
Private Sub Submit_Click()
Dim ws As Worksheet
Set ws = activesheet
[Code]....
View 9 Replies
View Related
Feb 15, 2013
I know you can use the syntax range(cells(x,y),cells(z,w)) but for some reason unknown to be the following isn't working:
Set CurrSuppRange = TT.Range(Cells(1, CurrSuppCol), Cells(LastRow, CurrSuppCol))
where
CurrSuppCol = cboxCurrSupp.ListIndex
OurSuppCol = cboxOurSupp.ListIndex
LastRow = Workbooks(lbWkBkName.Caption).Worksheets(cboxWorksheet.Value).Cells(Rows.Count, "A").End(xlUp).Row
(each return integer values)
Set TT = Workbooks(lbWkBkName.Caption).Worksheets(cboxWorksheet.Value)
(tested and works fine)
View 2 Replies
View Related
Aug 29, 2007
I have code that opens a workbook then selects a range on a specific sheet. All the code works fine (the workbook opens, then the sheet is selected) up until it gets to:
Range("BM9").Select
Then I get a run time error 1004
I tried Cells(9,65) it does the same thing,
View 9 Replies
View Related
Aug 3, 2006
The problem lies in the following line
Range(Cells(1, a), Cells(b, a)).Formula = _
"=(rc[-1]-" & min & ")/(" & max & "-" & min & ") "
When it is executed i get the 1004 error ("application-defined or object-defined error)...
min and max are defined (as worksheetfunction.min and max of a selection), and their values are correct. The problem seems to lie in the max variable, namely if i simplify the formula to just
Range(Cells(1, a), Cells(b, a)).Formula = "=(rc[-1]-" & min & ")"
it works ok, but if it is
Range(Cells(1, a), Cells(b, a)).Formula = "=(rc[-1]-" & max & ")"
I get the error.
I am completely baffled considering both variables are defined in the same way i.e.
min = Application.WorksheetFunction.min(Selection)
max = Application.WorksheetFunction.max(Selection)
View 9 Replies
View Related
Jan 12, 2007
I have a workbook that I am getting the error select method of range class failed. I am not sure what I have done wrong? I have a worksheet called Unit Tools that I would like to import the information in column B from another worksheet called Tool Catlog. This is the code that I used and I am getting a run time error 1004 with the above message. I am not really great with VBA but I am learning from the examples and questions that people have put on here.
Private Sub CommandButton4_Click()
Sheets("Tool Catalog").Select
Range("B3:B173").Select
Selection.Copy
Sheets("Unit Tools").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
View 5 Replies
View Related
May 9, 2007
I'm trying to find the last row on a sheet and then set the next cell, in column A to a certain value. It fails with Method 'Range' of object '_Worksheet' failed.
'activate the male page
sMalePicks.Activate
'add to last row of male baggage
lastRow = sMalePicks.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
'get next row
lastRow = lastRow + 1
sMalePicks.Range(Cells(lastRow, 1), Cells(lastRow, 1)) = Trim(bagNames(x))
View 5 Replies
View Related
Oct 4, 2009
Private Sub Worksheet_Activate()
Sheets("Search Inv").Protect UserInterFaceOnly:=True
ActiveSheet.Range("A17:F37,C39:C42,F39:F42").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.ClearContents
End Sub
Above is meant to clear the contents of cells that contain formulas while keeping the formulas.
The sheet is protected ... no password
What is wrong ... when I activate the sheet I get: Run Time error 1004 / no cells found
(All cells that are meant to be cleared are selected ... they just don't clear)
View 9 Replies
View Related
May 11, 2007
I'm getting a Run-time Error 1004 when using a Range(rangestring).Select.
It only seems to occur when I'm trying to select more than 35 cells. Up until 35 it works just fine.
My code is designed to select individual cells in a worksheet based on user selections from a list, and as I said, works fine unless they select more than 35 items.
Is there a limit to how many cells can be selected using Range...Select? If so, I can't find it documented anywhere.
View 14 Replies
View Related
Feb 1, 2010
I'm trying to do a simple loop which creates charts based on an ID number. I recorded a macro and has tried to modify it but am having trouble defining the correct reange when settign the data source. Here is my
View 2 Replies
View Related
Jul 23, 2013
I am trying to protect cells & password protect my workbook on closing. Here is my code. Not only is the cell locking code not working, but it is also preventing the code from re-hiding the columns ("P:P,R:R,W:W,U:U,AA:AA"). Here is my code; I've tested it on a blank sheet & it was working:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Application.EnableEvents = False
[Code].....
View 2 Replies
View Related
Jun 11, 2008
http://www.mrexcel.com/forum/showthread.php?t=323741
From the above thread I am using the following macro...
Sub MoveOver()
Dim MoveRange As Range
Application.ScreenUpdating = False
With Range("B1", Range("B" & Rows.Count).End(xlUp).Offset(, 21))
.AutoFilter field:=1, Criteria1:="=C&P"
On Error Resume Next
Set MoveRange = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
.AutoFilter
End With
If Not MoveRange Is Nothing Then
MoveRange.Insert Shift:=xlToRight
End If
Application.ScreenUpdating = True
End Sub
I am getting an error 1004 message saying cells cannot be moved off of the spreadsheet. However, my last cell on the sheet is J28956.
Columns B through J contain data. Columns beyond J do not contain data.
Can someone give advice on getting the macro to work.
View 9 Replies
View Related
Sep 25, 2008
This macro starts out looking for the first non blank cell by looking down about a hundred cells and then going back up till it finds the first non blank cell. When it finds the first non-blank cell it goes up 1 row and adds 1 to the number for the next row. It works fine the first time then, after the file is saved it will stop the macro with the following message; Run-time error ‘1004’ No cells were found. If I format the cells in question, the macro works again until I save the file, then it happens again.
Excel 2003 11.8220.8221 SP3
I think there is a hotfix for this but I can't get it to install. Is there a way around this?
This is the portion of the code that gets the error:
Range("A11:A100").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
ActiveCell.FormulaR1C1 = "=+R[-1]C+1"
View 9 Replies
View Related
Feb 9, 2012
I'm attempting to write an OutLook 2007 macro that extracts excel spreadsheets from incoming email and saves them as a .csv file. Before saving the file I need to delete the header row. I am getting a "Error 1004 - Delete method of range class failed" error when running this code.
Here is the code: (the bolded line is where I'm getting the error)
Public Sub ConvertToCsv2()
Dim xls As Excel.Application
Dim oWB As Excel.Workbook
Dim tmp As String
Dim ws As Excel.Worksheet
[code]....
View 7 Replies
View Related
