I am using a macro to autoformat a groups of cells(6x2) when a specific cell R[4]c[1] has specific information (below example, if said cell has "MERCH" in it, the group of cells should turn pink).
When i select cells q34:r39 and run the macro formula comes out as r38="MERCH". only cell q34 will change color. if the output were $R$34="Merch" the whole cluster of cells would turn.
[code]
' Macro8 Macro
'
'
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=R[4]C[1]=""MERCH"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.799981688894314
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
[code]
a little background if there is an overall better way.
i am trying to format a seating chart to color code agents by what work type they support. they move around a lot and it is a lot of upkeep manually updating them all the time. I can do all of this manually but i actually have about 7-8 different colors i need to be able to change to and over 200 desks to code which means that it will take me days to complete and if somethign happens to the document all my work is lost. if i can make a macro to at least format each desk's color settings one at a time i can just run the macro 200 times which is a lot faster than manually changing the formatting code of all 200 clusters.
I need a macro that will allow make to select a range and where the formulas are absolute to make these relative
Your assistance will be most appreciated
Howard
I suppose the answer is Not, but I'll ask it anyway.
Whenever I use the macro recorder to record a macro, all the references to a cell are absolute and not relative. If I am in A1 and I move to A2, it writes
Range("A2").Select
But for a programming-illiterate such as me, it would be terribly useful if instead it said:
ActiveCell.Offset(1, 0).Select
Is there any way for the macro recorder to provide that kind of code? Any other alternative sources?
I have a range of cells, with vey simple formula like
=b1
=b6
=c9
=d80
This is only one example column but there are other columns beside it. I would like to make the rows absolute in this type of range without going to every cell and entering F4 multiple times and also without using a macro.
Is there a way I can highlight the range and make it all absolute or give it mixed reference.
I know one can highlight range and enter formula in all of them at once by pressing Ctrl and then hitting enter, is there a substitute like that to change cell reference in a range
Hello all,
I have found the following macro online which is supposed to allow me to select multiple cells and have the formulas within them change from relative to absolute references. Unfortunately the macro does not seem to work for me. Not being a VBA expert, can somebody please look at the following code and see if they can spot the error? Very much appreciated.
Sub MakeAbsoluteorRelativeFast()
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim RdoRange As Range
Dim i As Integer
Dim Reply As String
'Ask whether Relative or Absolute
Reply = InputBox("Change formulas to?" & Chr(13) & Chr(13) _
& "Relative row/Absolute column = 1" & Chr(13) _
& "Absolute row/Relative column = 2" & Chr(13) _
& "Absolute all = 3" & Chr(13) _
& "Relative all = 4", "OzGrid Business Applications")
'They cancelled
If Reply = "" Then Exit Sub
On Error Resume Next
'Set Range variable to formula cells only
Set RdoRange = Selection.SpecialCells(Type:=xlFormulas)
'determine the change type
Select Case Reply
Case 1 'Relative row/Absolute column
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)
Next i
Case 2 'Absolute row/Relative column
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)
Next i
Case 3 'Absolute all
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
Next i
Case 4 'Relative all
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)
Next i
Case Else 'Typo
MsgBox "Change type not recognised!", vbCritical, _
"OzGrid Business Applications"
End Select
'Clear memory
Set RdoRange = Nothing
End Sub
Hello Bos ? anyone can help me, please..
How to change from absolute references to relative references.
Example :
ws.Range("G" & NextRow).Formula = "=" & Range("H" & NextRow).Address & "+" & ws.Range("I" & NextRow).Address
This code return the absolute references---> =$H$365+$I$365
, and i want change to relative references, like this ---> =H365+I365
Thank's b4
Hello all,
I am just learning VBA code and I came across the following macro:
Code:
Sub AddCheckBoxes()
On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
With Selection
.LinkedCell = c.Address
.Characters.Text = ""
.Name = c.Address
End With
c.Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & c.Address & "=TRUE"
.FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked
.FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked
.Font.ColorIndex = 2 'cell background color = White
End With
Next
myRange.Select
End Sub
It is pretty neat--It adds a checkbox (of the "forms toolbar" type) and links it to each cell that has been selected. The check boxes when clicked turn their linked cells yellow. It really increases user-friendliness.
The only complaint I have is that the checkboxes end up being linked to absolute cell references. I want to be able to sort and filter my data and keep the checkboxes with their rows even when I move them around.
Is there any way to do this? I was hoping someone could very quickly modify the above code to produce this result.
I am also open to any other easier method that someone could suggest.
Thanks in advance,
Nick
Hi,
I have a macro that add borders to a specific range. Here is the code :
VB:
Sub addborders()
With Worksheets("Documents")
.Range("e1", .Range("e" & Rows.Count).End(xlUp)) _
.Offset(, -4).Resize(, 12).Borders.Weight = xlThin
End With
End Sub
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
I would like to change it to be able to call it with something like this : addborder("a1:l" & lastrow) (or something more efficient) where lastrow is the last row of the last column of the range : Range("l" & Rows.Count).End(xlUp))
How do I write this?
Thank you for your help
Hi, i have a VLOOKUP formula in column A the table range in the formula is absolute, i want to copy the formula to column e, obvisously the formula brings with the absolute ref.
Is there an easy way to covert to relative ref when the formula is copied to column E rather than me pressing F4 in column A and then copy the formula to COL E then go back to COL A and make the cells Absolute again.
Jamie
