Excel Macro conditional formatting indirect cell
I'm a little inexperienced with Excel, but I'll try my best to explain what I'm trying to do:
I'm working in Excel 2010, and I'm trying to have a macro do an addition based on the number in a column.
For example, I want to have the macro add a value based on the name in the B column and the number in the C column. For the name "02 Gloves-DISC" in the B column, I wanted to add the following based on the value in the C column: if it's <5, +8.83. if it's <10, +7. if it's <20, +5. if it's <30, +3. if it's <40, +1. if it's <56, +.50.
I had something similar to this, but I could not get it to do the search and calculation for each row:
Selection.Replace What:="02 Gloves-DISC", Replacement:="=IF(C2<5, C2+8.83, IF(C2<10, C2+7, IF(C2<20, C2+5, IF(C2<30, C2+3, IF(C2<40, C2+1, C2+.5)))))", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False
I hope this info helps. Thank you in advance for any help. I've read about the R1C1 reference, but I cannot seem to get it to work.
Here is an example of the data I'm working with:
SKU ClassName TakeItPrice HJC10569002 02 Gloves-DISC 38.93 HJC1222066 02 Gloves-DISC 49.491 HJC1224011 02 Gloves-DISC 40.491 HJC1228062 02 Gloves-DISC 36.991 HJC152100 01 First Class-DISC 13.191 HJC152200 01 First Class-DISC 26.99 HJC152202 01 First Class-DISC 31.491 HJC180000 01 First Class-DISC 11.891 HJC190005 01 First Class-DISC 11.891 HJC350005 01 First Class-DISC 11.891
Two macros for you.. the second is a generic one I use to find the last row of data in a sheet.
Public Sub addDisc() Dim Class As Long Dim Price Dim myRow For myRow = 2 To xlLastRow(ActiveSheet.Name) 'Optional to find last row Class = CLng(Left(Cells(myRow, 2), 2)) If Class < 5 Then Cells(myRow, 3) = Cells(myRow, 3) + 8.83 ElseIf Class < 10 Then Cells(myRow, 3) = Cells(myRow, 3) + 7 ElseIf Class < 20 Then Cells(myRow, 3) = Cells(myRow, 3) + 5 ElseIf Class < 30 Then Cells(myRow, 3) = Cells(myRow, 3) + 3 ElseIf Class < 40 Then Cells(myRow, 3) = Cells(myRow, 3) + 1 ElseIf Class < 56 Then Cells(myRow, 3) = Cells(myRow, 3) + 0.5 End If Next myRow End Sub Public Function xlLastRow(Optional WorksheetName As String) As Long ' find the last populated row in a worksheet If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name With Worksheets(WorksheetName) On Error Resume Next xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _ xlWhole, xlByRows, xlPrevious).Row If Err <> 0 Then xlLastRow = 0 End With End Function