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
Target.Font.ColorIndex = 3
End If
End If
End Sub

View 4 Replies


Error 1004 Using Range.Cells() Properties In Vba

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

Range(cells(),cells()) From 2 Different Workbooks, Run Time Error 1004

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

Fill Range Of Cells With A Colour - Keep Getting Error

Dec 5, 2013

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

Range Change Induces Error "1004"?

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
Exit For
End If
Next ws...............

View 4 Replies View Related

Change A Worksheet Tab Colour Dependant On The Contents On A Range Of Cells?

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

VBA Code To Change Font Colour Of Cell Referenced Letter Strings Within Range Of Cells

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

ComboBox Change Event Gives Error 1004

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

Worksheets("SAVED").Range("A" & l).Select

Dim l As Long
Application.ScreenUpdating = False
l = Application.WorksheetFunction.Match(Worksheets("DATA").Range("O34"), Worksheets("SAVED").Range("A1:A10000"), 0)
Worksheets("SAVED").Range("A" & l).Select
Selection.Resize(1, 739).Offset(1, 2).Copy


View 2 Replies View Related

Change History Duration Error '1004'

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

Runtime Error 1004 - Cannot Change Part Of Merged Cell?

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


View 3 Replies View Related

Runtime Error 1004 - Cannot Change Part Of Merge Cell

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
ActiveWorkbook.SaveEnd Sub

View 1 Replies View Related

Run-time Error 1004 Upon Paste Firing Change Event

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

Error 1004 With Range To Paste?

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

Range Offset Error 1004

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

Error 1004 Using Variable Range

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").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

Run-time Error '1004' :: Range ..

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

Finding Last Cell In Range - 1004 Error

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

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

Error 1004 - Range Of Object Failed?

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.

Private Sub Submit_Click()
Dim ws As Worksheet
Set ws = activesheet


View 9 Replies View Related

Syntax Range - Getting Runtime Error 1004

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))
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

Run Time Error 1004 For A Range Selection

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:

Then I get a run time error 1004

I tried Cells(9,65) it does the same thing,

View 9 Replies View Related

Variable In Range.Formula Causes 1004 Error

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

Select Range Failed, Error 1004?

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
Sheets("Unit Tools").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

View 5 Replies View Related

Setting Range To Value Fails With 1004 Error

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

'add to last row of male baggage
lastRow = sMalePicks.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _

'get next row
lastRow = lastRow + 1

sMalePicks.Range(Cells(lastRow, 1), Cells(lastRow, 1)) = Trim(bagNames(x))

View 5 Replies View Related

No Cells Found Error: 1004

Oct 4, 2009

Private Sub Worksheet_Activate()
Sheets("Search Inv").Protect UserInterFaceOnly:=True
Selection.SpecialCells(xlCellTypeConstants, 23).Select
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

Run-time Error 1004 When Using A Range(rangestring).Select

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

Setting A Data Range For A Chart (error 1004).

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

Runtime Error 1004 - Cells Not Locking?

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


View 2 Replies View Related

Error 1004 When Trying To Move Cells 24 Cols To The Right

Jun 11, 2008

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
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

Run-Time Error 1004 No Cells Were Found

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:

ActiveCell.FormulaR1C1 = "=+R[-1]C+1"

View 9 Replies View Related

Delete Method Of Range Class Failed - Error 1004

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


View 7 Replies View Related

Copyrights 2005-15, All rights reserved