Get instant live Excel expert help to learn how to find range in Excel

Range("B6").Select If Value = "3" Then Range("AI54").Select Else: Range("AI5").Select End If B6 is =COUNTIF(AI54:AM75,"1") How to make it work properly as the If Value seems to take in consideration the formula and not the result of it.

I'm doing a VBA Macro. I need to use a cell value date content (which will change with each new report) and subtract 2 days from it in order to find the new value in a Range and then gray it out.

Private Sub FedexPart3(SKUCOL, FUNSKUCOL, QTYCOL, SELLINGCOL, TOTALCOL, WEIGHTCOL, COMMODCoL, DESCCOL, ADDRESSCOL, BoxLetterCol, ASINCOL, EANCOL, FBASHIPCOL, BOXQTYCOL, BoxWeightCol, BoxDimsCol, FBAIDCOL, ADDCVDCOL, SHIPLETTERCOL, CVDCOL) shipid = InputBox(Prompt:="FBA Shipment ID", Title:="Please Enter the FBA Shipment ID you wish to process") planid = InputBox(Prompt:="Shipment Plan ID?", Title:="Enter Shipment Plan ID") shipdate = InputBox(Prompt:="Please enter an Shipment Date in the following format: 'dd/mm/yyyy'", Title:="Enter Shipment Date") Call clearinvoices Call GetHeader(shipid, shipdate) Application.ScreenUpdating = False 'Create Folder Directories basedir = ActiveWorkbook.Path & "\Shipments\" If Len(Dir(basedir, vbDirectory)) = 0 Then MkDir (ActiveWorkbook.Path & "\Shipments\") End If If Len(Dir(basedir & shipid & "\", vbDirectory)) = 0 Then MkDir (basedir & shipid & "\") End If If Len(Dir(basedir & shipid & "\" & "Commercial Invoice" & "\", vbDirectory)) = 0 Then MkDir (basedir & shipid & "\" & "Commercial Invoice" & "\") End If commercialdir = basedir & shipid & "\" & "Commercial Invoice" & "\" If Len(Dir(basedir & shipid & "\" & "Packing Slips" & "\", vbDirectory)) = 0 Then MkDir (basedir & shipid & "\" & "Packing Slips" & "\") End If packingdir = basedir & shipid & "\" & "Packing Slips" & "\" 'Create Packing Slips Sheets("MASTERSHEET").Select m = 2 Range("T2").Select Selection.Sort key1:=Range("T2:T65536"), order1:=xlAscending, Header:=xlYes Range("R2").Select Selection.Sort key1:=Range("R2:R65536"), order1:=xlAscending, Header:=xlYes mancnt = Sheets("MASTERSHEET").Range("R65536").End(xlUp).Row startrow = 12 lastboxchar = Range(BoxLetterCol & m).Value boxno = 1 While m <= mancnt + 1 Sheets("MASTERSHEET").Select fbaid = Range(FBAIDCOL & m) boxchar = Range(BoxLetterCol & m).Value sku = Range(SKUCOL & m) commod = Range(COMMODCoL & m) desc = Range(DESCCOL & m) totalqnt = Round(Range(BOXQTYCOL & m), 0) If shipid = Trim(fbaid) Then If lastboxchar = boxchar Then Sheets("PACKING SLIPS").Select If Not Sheets("PACKING SLIPS").Range("D" & startrow) = "" Then Range("A" & startrow).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove End If Range("A" & startrow & ":C" & startrow).Merge Range("A" & startrow) = sku & " " & desc Range("D" & startrow) = commod Range("E" & startrow) = Round(totalqnt, 0) Range("A" & startrow).WrapText = True Range("A" & startrow).RowHeight = 40 lastboxchar = boxchar m = m + 1 Else If Not lastboxchar = "" Then Sheets("PACKING SLIPS").Select Sheets("PACKING SLIPS").Range("A7") = "Box" & " " & boxno Sheets("PACKING SLIPS").Range("B7") = "Box" & " " & lastboxchar Sheets("PACKING SLIPS").Range("E" & startrow) = Application.WorksheetFunction.Sum(Range("E12:E" & startrow - 1)) 'print packing slips ActiveSheet.PageSetup.PaperSize = xlPaperA4 ActiveSheet.PageSetup.PrintArea = "A1:E" & startrow ActiveSheet.PageSetup.Orientation = xlLandscape ' ****************************************** ' Reactivate Prints after development ' ActiveSheet.PrintOut ' ****************************************** Call savepackingslips(boxno, shipid, packingdir) startrow = 11 boxno = boxno + 1 lastboxchar = Sheets("MASTERSHEET").Range(BoxLetterCol & m).Value Call resetpackingslips Else lastboxchar = Sheets("MASTERSHEET").Range(BoxLetterCol & m).Value End If End If startrow = startrow + 1 Else m = m + 1 End If Wend 'Save final packing slip in shipment Sheets("PACKING SLIPS").Select Sheets("PACKING SLIPS").Range("A7") = "Box" & " " & boxno Sheets("PACKING SLIPS").Range("B7") = "Box" & " " & lastboxchar Sheets("PACKING SLIPS").Range("E" & startrow) = Application.WorksheetFunction.Sum(Range("E12:E" & startrow - 1)) ActiveSheet.PageSetup.PaperSize = xlPaperA4 ActiveSheet.PageSetup.PrintArea = "A1:E" & startrow ActiveSheet.PageSetup.Orientation = xlLandscape 'ActiveSheet.PrintOut Call savepackingslips(boxno, shipid, packingdir) 'Create Commercial Invoice c = 2 itemstartcell = 22 Dim done As String 'Create Table Headers Sheets("COMMERCIAL INVOICE").Range("B21") = "QTY" Sheets("COMMERCIAL INVOICE").Range("C21") = "Description" Sheets("COMMERCIAL INVOICE").Range("D21") = "HS Code" Sheets("COMMERCIAL INVOICE").Range("E21") = "ADD/CVD Details" Sheets("COMMERCIAL INVOICE").Range("F21") = "EAN" Sheets("COMMERCIAL INVOICE").Range("G21") = "Weight (KG)" Sheets("COMMERCIAL INVOICE").Range("H21") = "Country Of Origin" Sheets("COMMERCIAL INVOICE").Range("I21") = "Unit Selling Price (USD)" Sheets("COMMERCIAL INVOICE").Range("J21") = "Total Selling Price (USD)" While c <= mancnt Sheets("MASTERSHEET").Select funsku = Range(FUNSKUCOL & c) fbaid = Range(FBAIDCOL & c) If shipid = Trim(fbaid) Then strpos = InStr(done, funsku) 'Debug.Print strpos If Not strpos >= 1 Then Address = Range(ADDRESSCOL & c) commod = Range(COMMODCoL & c) desc = Range(DESCCOL & c) kg = Range("J2:J" & c) sku = Range(SKUCOL & c) EANCode = Range(EANCOL & c) cvd = Range(CVDCOL & c) sellingprice = Range(SELLINGCOL & c) Set ws = ThisWorkbook.Sheets("MASTERSHEET") Set vistotal = ws.Range("W1:W" & mancnt) Set sortsku = ws.Range("A1:A" & mancnt) Set kg = ws.Range("P2:P" & mancnt) ws.AutoFilterMode = False Sheets("MASTERSHEET").Range("I1").AutoFilter Field:=9, Criteria1:=funsku totalweight = Application.WorksheetFunction.Sum(kg.SpecialCells(xlCellTypeVisible)) visibleTotal = Application.WorksheetFunction.Sum(vistotal.SpecialCells(xlCellTypeVisible)) Sheets("COMMERCIAL INVOICE").Select If Not Sheets("COMMERCIAL INVOICE").Range("I" & itemstartcell) = "" Then Range("B" & itemstartcell).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow End If Range("B" & itemstartcell) = Round(visibleTotal, 0) Range("B" & itemstartcell).HorizontalAlignment = xlCenter Range("C" & itemstartcell) = desc & " " & sku Range("C" & itemstartcell).WrapText = True Range("C" & itemstartcell).Rows.AutoFit Range("C" & itemstartcell).HorizontalAlignment = xlLeft Range("D" & itemstartcell) = commod Range("D" & itemstartcell).HorizontalAlignment = xlCenter Range("D" & itemstartcell).NumberFormat = "0" Range("E" & itemstartcell) = cvd Range("E" & itemstartcell).HorizontalAlignment = xlCenter Range("F" & itemstartcell) = EANCode Range("F" & itemstartcell).HorizontalAlignment = xlCenter Range("F" & itemstartcell).NumberFormat = "0" Range("G" & itemstartcell) = Format(totalweight, "#.000") Range("G" & itemstartcell).HorizontalAlignment = xlCenter Range("H" & itemstartcell) = Address Range("H" & itemstartcell).WrapText = True Range("H" & itemstartcell).Rows.AutoFit Range("H" & itemstartcell).HorizontalAlignment = xlLeft Range("H" & itemstartcell).VerticalAlignment = xlCenter Range("I" & itemstartcell) = sellingprice Range("I" & itemstartcell).HorizontalAlignment = xlLeft Range("J" & itemstartcell) = sellingprice * visibleTotal Range("K" & itemstartcell) = sku itemstartcell = itemstartcell + 1 End If c = c + 1 done = done & funsku & ", " 'Debug.Print done On Error Resume Next Sheets("MASTERSHEET").ShowAllData Else c = c + 1 End If Wend Sheets("COMMERCIAL INVOICE").Select lrow = Application.WorksheetFunction.Match("QTY", Range("b1:b200"), 0) 'Calculate total QTY, WEIGHT & SELLING PRICE Sheets("COMMERCIAL INVOICE").Range("D8") = "$" & Application.WorksheetFunction.Sum(Range("J" & lrow + 1 & ":J" & itemstartcell - 1)) Sheets("COMMERCIAL INVOICE").Range("B" & itemstartcell + 1) = Application.WorksheetFunction.Sum(Range("B" & lrow + 1 & ":B" & itemstartcell - 1)) Sheets("COMMERCIAL INVOICE").Range("J" & itemstartcell + 1) = Application.WorksheetFunction.Sum(Range("J" & lrow + 1 & ":J" & itemstartcell - 1)) Sheets("COMMERCIAL INVOICE").Range("G" & itemstartcell + 1) = Application.WorksheetFunction.Sum(Range("G" & lrow + 1 & ":G" & itemstartcell - 1)) 'update commercial invoice with box weights and dims Sheets("COMMERCIAL INVOICE").Select c = 2 While c <= mancnt boxid = Split(Sheets("MASTERSHEET").Range("T" & c), "B") Sheets("MASTERSHEET").Range("X" & c) = Int(boxid(1)) c = c + 1 Wend ' ActiveWorkbook.Worksheets("MASTERSHEET").Sort.SortFields.Clear ' ActiveSheet.Worksheets("MASTERSHEET").Range("$A$1:$W$117").AutoFilter Field:=19, Criteria1:=shipid ' Debug.Print "Filter Sheet" startcell = 11 oddstartcell = 11 evenstartcell = 11 boxcnt = 1 ActiveWorkbook.Worksheets("MASTERSHEET").Sort.SortFields.Clear ActiveWorkbook.Worksheets("MASTERSHEET").Sort.SortFields.Add Key:=Range("X1") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("MASTERSHEET").Sort .SetRange Range("A2:X881") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Selection.AutoFilter ActiveSheet.Range("$S$1:$S$881").AutoFilter Field:=1, Criteria1:=shipid ' ActiveWorkbook.Worksheets("MASTERSHEET").AutoFilter.Sort.SortFields.Add Key:= ' Range ("X1:X117"), Header:=xlYes, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _ ' :=xlSortNormal ' With ActiveWorkbook.Worksheets("MASTERSHEET").AutoFilter.Sort ' .Header = xlYes ' .MatchCase = False ' .Orientation = xlTopToBottom ' .SortMethod = xlPinYin ' .Apply ' End With ' Debug.Print "Sort Sheet" c = 2 lastboxno = "1" While c <= mancnt fbaid = Sheets("MASTERSHEET").Range(FBAIDCOL & c) boxchar = Sheets("MASTERSHEET").Range(BoxLetterCol & c) boxweight = Sheets("MASTERSHEET").Range(BoxWeightCol & c) boxdim = Sheets("MASTERSHEET").Range(BoxDimsCol & c) If Len(boxchar) = 2 Then no = 1 Else no = 2 End If boxno = Right(boxchar, no) '****************************************************************** ' Needs to take into account multiple lines for each box If boxno = lastboxno Then If boxno Mod 2 = 0 Then Range("F" & oddstartcell) = boxchar Range("G" & oddstartcell) = Range("G" & oddstartcell) + boxweight Range("H" & oddstartcell) = boxdim lastbox = boxchar Else Range("B" & evenstartcell) = boxchar Range("C" & evenstartcell) = Range("C" & oddstartcell) + boxweight Range("D" & evenstartcell) = boxdim End If Else If boxno Mod 2 = 0 Then If boxno > 2 Then oddstartcell = oddstartcell + 1 End If Range("F" & oddstartcell) = boxchar Range("G" & oddstartcell) = boxweight Range("H" & oddstartcell) = boxdim lastbox = boxchar boxcnt = boxcnt + 1 Else evenstartcell = evenstartcell + 1 Range("B" & evenstartcell).EntireRow.Insert Shift:=xlDown Range("B" & evenstartcell) = boxchar Range("C" & evenstartcell) = boxweight Range("D" & evenstartcell) = boxdim lastbox = boxchar boxcnt = boxcnt + 1 End If End If c = c + 1 lastboxno = boxno Wend ActiveSheet.ShowAllData 'decval = Sheets("COMMERCIAL INVOICE").Range("D8").Value / 1.25 * 100 'Sheets("COMMERCIAL INVOICE").Range("F8") = "$" & decval Sheets("COMMERCIAL INVOICE").Range("D9") = boxcnt Sheets("COMMERCIAL INVOICE").Range("D9").HorizontalAlignment = xlLeft 'Print Commercial Invoice Sheets("COMMERCIAL INVOICE").Select ActiveSheet.PageSetup.PaperSize = xlPaperA4 ActiveSheet.PageSetup.PrintArea = "A1:I" & itemstartcell + 2 ActiveSheet.PageSetup.Orientation = xlLandscape ' ****************************************** ' Reactivate Prints after development ' ActiveSheet.PrintOut ' ****************************************** 'Call SavecommercialPDF(shipid, commercialdir) Call createpacklist(shipid, planid, SKUCOL, FUNSKUCOL, QTYCOL, SELLINGCOL, TOTALCOL, WEIGHTCOL, COMMODCoL, DESCCOL, ADDRESSCOL, BoxLetterCol, ASINCOL, EANCOL, FBASHIPCOL, BOXQTYCOL, BoxWeightCol, BoxDimsCol, FBAIDCOL, ADDCVDCOL, SHIPLETTERCOL, CVDCOL) End Sub The error or debug Sheets("MASTERSHEET").Range("X" & c) = Int(boxid(1)) boxid(1) = <subscript out of range>

I need help on conditional formatting dates. So in short, I need the cells to be red if the date is between the 1st and 14th, then green if the date is between the 15th and 30th (or 31st). The current formula I am using is the following: RED =TODAY()>DATE(2017,8,1) =TODAY()>DATE(2017,9,1) =TODAY()>DATE(2017,10,1) =TODAY()>DATE(2017,11,1) =TODAY()>DATE(2017,12,1) GREEN =TODAY()>DATE(2017,8,15) =TODAY()>DATE(2017,9,15) =TODAY()>DATE(2017,10,15) =TODAY()>DATE(2017,11,15) =TODAY()>DATE(2017,12,15) So, I went in to the "Conditional Formatting" then went to manage rules, add rule. Then I did it in order of month, red to green. For example I would copy and paste the 8/17 red code and then the 08/17 green code, in order up to December 2017. It is now the 20th and I opened the excel sheet to check if the cell would be green because the date range but it is not. I have an attachable image.

Hello, I am looking to highlight the cells that have text in them to highlight as red between the 1st through the 15th of every month, and then be green the 16th through last day of the month whether it be the 30th, 31st, or even 28th (depends on the month). I attached a sheet in which I was testing by highlighting all of the cells with text (the 1 of 8, 2 of 4, etc.) then clicking "Conditional Formatting" > "Highlight Cells Rules" > and then either Greater Than or Less Than for both red and green using the following formulas for each month: Less Than (Red): 2017-08-15 2017-09-15 2017-10-15 2017-11-15 2017-12-15 Greater Than (Green): 2017-08-15 2017-09-15 2017-10-15 2017-11-15 2017-12-15 I am not sure what I am doing wrong but when I did the formula and chose the colors for August, it seemed to work fine, being today's date would be within the right date range for the cells to highlight as green. I then removed the August formulas and replaced them with September formulas, it did not seem to work.

Hi I am looking for help on conditional formatting I have two ranges of 11 cells and I would like a singular cell in the first range to flash red if it less than the bottom range, could you please assist?

Into the cell G1 I wish to use VLOOKUP to determine the examination score of the student named in cell F1. The range of cell A1:D77 is given the range name "results" Fill in the blank part of the statement =VLOOKUP( , , ,TRUE)

I'm trying to use a formula to get a date associated with a final entry and found this one which works very well: =LOOKUP(2,1/(2:2<>""),$1:$1) -note: 2:2 refers to the row, and $1:$1 refers to the header. The problem is that I need to exclude the cells that are subtotals of the range of cells in question. I'm having trouble finding the same formula that will instead give me the second last entry or even just exclude the cells that I want to ignore. The attachment is just an illustration of how I'm using the formula and where I'm having trouble.

Sub Worksheet_Change(ByVal Target As Range) If Range("C18") < 1 Then MsgBox "You will run out of B101 bottles" End If End Sub Sub Worksheet_Change(ByVal Target As Range) If Range("C19") < 1 Then MsgBox "You will run out of B102 bottles" End If End Sub

When I change the name in E1, (Name range is A2 to A117). Relevant branches (B2 to B117) should list down from E2 onwards.