I need Macros help!!!

[copasln]

I have to run a report weekly that displays warehouse inventory going out. I have to ensure everything is coded correctly. This report contains 500-1000 rows and can take a significant amount of time to review. I want to run a macro that will automatically color code a group and product number but not change the rest of the data from my report.

Can I run the report, save it, then Record Macro coloring what I want colored? Then next week, run the report, open recorded macro file, run macro to color new report??? Or at least thats what I envision.

Random example of the report (below).

What I'd like the report to look like after I run a Macro or something (below).

Any macro help or whatever would be greatly appreciated.
THANK YOU!!!!!!!!!!

 

[copasln]

So is the file, read only.
You will need to enable editing
Have a look on the Review Tab and see if the workbook is protected.

The file is not read only.
The review tab shows the sheet is not protected, the workbook is not protected.
Is there another way to do this? I just want to color 9 different groups and corresponding numbers that fall into these groups.

 

[rajendradk9]

You Can Try Conditional Formatting, no need Macro, Try in excel shortcut ALT+H+L+N then below POP will come where you can do the conditional formatting. Hope you're looking same.

 

[copasln]

You Can Try Conditional Formatting, no need Macro, Try in excel shortcut ALT+H+L+N then below POP will come where you can do the conditional formatting. Hope you're looking same.

View attachment 104983

This looks good but I also need the product number to match the color of the group it belongs in. And there is a lot of product numbers, not just a couple.

 

[Fluff]

my sheet is not protected

Have a look here XL2BB Icons greyed out

 

[rajendradk9]

This looks good but I also need the product number to match the color of the group it belongs in. And there is a lot of product numbers, not just a couple.

Can you Please try above formula, before doing this just select columns where you want to highlight, hope you're looking same.

 

[copasln]

View attachment 105036
Can you Please try above formula, before doing this just select columns where you want to highlight, hope you're looking same.

View attachment 105037

I was really hoping for a macro to do this for me because each week I will run the report and it's also timely to enter the conditional formating. I would need to apply the colors to several hundred cells each week.

 

[mumps]

Try:

Sub ColorCells()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, lRow As Long, x As Long: x = 3
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("F2:F" & lRow).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        If Not dic.exists(v(i, 1)) Then
            dic.Add v(i, 1), Nothing
            Range("B1:F" & lRow).AutoFilter 5, v(i, 1)
            Range("E2:F" & lRow).SpecialCells(xlVisible).Interior.ColorIndex = x
            x = x + 1
        End If
    Next i
    Range("B1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[copasln]

Try:

Sub ColorCells()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, lRow As Long, x As Long: x = 3
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("F2:F" & lRow).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        If Not dic.exists(v(i, 1)) Then
            dic.Add v(i, 1), Nothing
            Range("B1:F" & lRow).AutoFilter 5, v(i, 1)
            Range("E2:F" & lRow).SpecialCells(xlVisible).Interior.ColorIndex = x
            x = x + 1
        End If
    Next i
    Range("B1").AutoFilter
    Application.ScreenUpdating = True
End Sub

This is more complicated then I envisioned. I'm not sure how to do this.

 

[mumps]

Do the following: Save the workbook as a macro-enabled file. This will change its extension to "xlsm". Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

 

[copasln]

Do the following: Save the workbook as a macro-enabled file. This will change its extension to "xlsm". Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

When I try to run the Macro I get an error.