Re: Locking select worksheets
Format=>Cells=>Protection=>Locked
You could just make all cells on the worksheet locked apart from the ones you require to change, then protect the worksheet.
Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
Re: Locking select worksheets
Format=>Cells=>Protection=>Locked
You could just make all cells on the worksheet locked apart from the ones you require to change, then protect the worksheet.
Re: Run Time 1004 Error
Hi
could you not do this i.e make the sheet visible whilst the code is running then unhide again? If you turn off screen updating then it still cannot be seen whilst the sode is running:
Application.ScreenUpdating = False
Worksheets("YTDdb").Visible = xlSheetVisible
Worksheets("YTDdb").Activate
'your code i.e.set etc etc
Worksheets("YTDdb").Visible = xlSheetHidden
Application.ScreenUpdating = True
Hope that helps
Re: Doiing a loop based on the number of non-empty rows
Would it not be better to find the last row that has something entered into it then loop through all the rows above? You can then loop through all these rows and if nothing is entered in a particular row, dont copy that row etc. If this is the case then you require something like this:
Sub macro1()
Dim LastRow As Integer
Dim I As Integer
Dim rrng As Integer
Application.ScreenUpdating = False
LastRow = Range("A65536").End(xlUp).Row
For rrng = 2 To LastRow
With Cells(rrng, 1)
'insert code here
End With
Next rrng
Application.ScreenUpdating = True
End Sub
Display More
If you require any help on this just let me know.
Hope that helps
Re: If statements
Copy this statement and paste into C1
=IF(AND(A1=0,B1 = 0),0,IF(AND(A1=1,B1 = 1),1,IF(AND(A1=1,B1 = 0),0,IF(AND(A1=0,B1 = 1),0,""))))
Hope this helps
Re: Toggle Format
This works over a selection of cells that contain etiher type of formatting i.e if the selection contains blue cells they will turn white and white cells will turn blue:
Sub BlueShading()
Dim crng As Range
Set crng = Selection
For Each crng In Selection
If crng.Font.Color = RGB(255, 255, 255) Or Selection.Font.ColorIndex = -4105 Then 'White or Automatic
crng.Font.Color = RGB(0, 0, 0)
crng.Interior.ColorIndex = xlNone
crng.Font.Bold = False
ElseIf crng.Font.Color = RGB(0, 0, 0) Then 'Black
crng.Font.Color = RGB(255, 255, 255)
crng.Interior.Color = RGB(0, 28, 92)
crng.Font.Bold = True
End If
Next crng
End Sub
Display More
Hope this helps
Re: Toggle Format
It seems to work for me, but only when the selection contains only one style of formatting, if your selection contains both white and blue it doesnt work, was this what you were trying to achieve?
Re: Find and replace query
No Will not at all, I appreciate the help, I myself am fairly new to VBA. For a relative novice like myself, it is just nice to have code that works! I appreciate the help in understanding good coding techniques. I am teaching myself at the moment pretty much from scrath using this website, so the only way I will learn is if you guys explain why certain methods are used as opposed to others. It is very nice to have the most efficient code possible however being new it is near on impossible to know what is best.
Re: Search range, find value, copy then offset paste across 2 worksheets
I am finding it difficult to understand what you actually require?
Could you post a small example or elaborate a little?
Re: Find and replace query
Ammended above code slightly in order to get what u stated:
Sub age_bander()
Application.ScreenUpdating = False
i = 2
While i < 1600
Columns("d:d").Select
Range("d" & i).Activate
If ActiveCell.Value = "1" Then
ActiveCell.Value = "Under 18"
ElseIf ActiveCell.Value = "2" Then
ActiveCell.Value = "18 - 64"
ElseIf ActiveCell.Value = "3" Then
ActiveCell.Value = "65 - 74"
ElseIf ActiveCell.Value = "4" Then
ActiveCell.Value = "75+"
ElseIf ActiveCell.Value = "5" Then
ActiveCell.Value = "Age unknown"
End If
i = i + 1
Wend
Application.ScreenUpdating = True
End Sub
Display More
Hope this helps
Re: Find and replace query
IS this any good?
Sub age_bander()
Application.ScreenUpdating = False
i = 2
While i < 1601
Columns("d:d").Select
Range("d" & i).Activate
If ActiveCell.Value = "1" Then
ActiveCell.Value = "Under 18"
End If
i = i + 1
Wend
Application.ScreenUpdating = True
End Sub
Display More
Hope this helps
Matt
Re: Compare two colums to eachother
Hi again
removing the file extensions worked and the code will run now!! Happy days
Thanks for the help batman however it would be mostly appreciated if you could still post the example using conditional formatting as I may decide to use this instead and it will help my knoledge of excel. I can get it to work on the same sheet however referencing another sheet seems to cause problems.
All your help is greatly appreciated
Matt
Just got your example as i posted this will let you know if i get any other problems
Once again thanks v much
Re: Compare two colums to eachother
Hi Batman
I tried using the code you said but I still coundn't get it to work :?
So then I tried the Conditional Formatting and was getting that to work however when I tried to use other worksheets (like i was doing in the coded version) excel told me I may not use references from other worksheets or workbooks for conditional formatting which blows that option out of the water (which is kinda unforunate because a lot easier), even though it allows you to input reference to other workheets. So now Im back to my original problem. Is there any way you could post what you said you got to work?
This is what I did with your code, sorry if you think im being stupid!!
Option Explicit
Dim cCola As Range
Dim cColb As Range
Dim rngIRd As Range
Dim rngACs As Range
Dim Match As Boolean
Sub FindDubs()
Set rngIRd = Workbooks("Invoice Check.xls").Worksheets("invoices recieved").Range("A3:A10")
Set rngACs = Workbooks("Invoice Check.xls").Worksheets("All Cons").Range("A3:A10")
For Each cCola In rngIRd
Match = False
For Each cColb In rngACs
If cCola = cColb Then
Match = True
cColb.Interior.ColorIndex = 4
cCola.Interior.ColorIndex = 4
End If
Next cColb
If Match = False Then
cCola.Interior.ColorIndex = 3
End If
Next cCola
End Sub
Display More
Just gettin the same error as before
Thanks v much
Matt
Re: Compare two colums to eachother
Help needed again!!!!
The colums I'm using are on 2 different sheets and for some reason I can't seem to get this to work. Sorry if I'm being stupid, I am trying!! Here is what I've tried to do on top of Bills code.
Option Explicit
Dim cCola As Range
Dim cColb As Range
Dim rngIRd As Range
Dim rngACs As Range
Dim Match As Boolean
Sub FindDubs()
Windows("Invoice Check.xls").Activate
Worksheets("invoices recieved").Activate
Set rngIRd = Range("A3:A10")
Windows("Invoice Check.xls").Activate
Worksheets("All Cons").Activate
Set rngACs = Range("A3:A10")
For Each cCola In rngIRd
Match = False
For Each cColb In rngACs
If cCola = cColb Then
Match = True
cColb.Interior.ColorIndex = 4
cCola.Interior.ColorIndex = 4
End If
Next cColb
If Match = False Then
cCola.Interior.ColorIndex = 3
End If
Next cCola
End Sub
Display More
It keeps coming up with runtime error 1004
Unable to set the colourindex property of the interior class
Please could someone let me know what I'm doing wrong and explain it to me, it's doing my head in :yikes:
Many many thanks
Matt
By the way if anyone could let me know how to improve on my code, I know it properly isn't written in the best way, then please let me know. I know there are meant to be certain methods which are deemed as good practice however i don't know what these are (I'm only a beginner to VB). Thanks
Re: Compare two colums to eachother
Hi Gents
Thanks ever so much for the suggestions!! Tried both methods and couldnt get the conditional formatting to work (not too sure what i was doing wrong but I dont doubt it was me getting it wrong!) however the code worked a treat with no changes. Apologies if my question was none too clear originally. Really appreciate the help though.
Thanks Bill and Batman!!!!!
Matt
Hi all again
Quick one (I hope).... If i have two columns, what would be the best way to compare all the values in these two columns and if any values exist in both columns, albeit anywhere in these columns, then the cells in both columns are changed to green, whereas any cells that dont exist in both columns are changes red? Any suggestions? I can't think how to go about this.
Many Thanks in advance
Matt
Re: Force a cell to show entered text in Upper case
You could either use a font that is only a caps font or alternatively use the formula:
=UPPER([insert text or cell ref here])
Re: Range Select
Thanks guys, spent ages playing about with the quotes driving myself mental.
:thanx: