#1
|
|||
|
|||
Macro Needed with RGB
Hi all,
I have the following workbooks that I need your help with a macro. The workbook names are as follows: 025-FY 19-Monthly Report 050-FY 19-Monthly Report 056-FY 19-Monthly Report 100-FY 19-Monthly Report 130-FY 19-Monthly Report 135 FY 19-Monthly Report Everyone of this workbooks has the following sheets with the following names: FY 19 Budget FY 19 Actual FY 18 Actual FY 17 Actual FY 15 Actual In every sheet, there are between three and five rows (maybe less/more) with a RGB value of (0, 0, 0), which is black color. I'm hoping that with your help I can change the RGB value of (0, 0, 0) to a RBG value of (192, 192, 192). I don't know what will be the best method to accomplish this. Not sure if we can use Find and Replace option or if an IF option can do it. I'm attaching a copy of one of the files so you can see the colors in the rows. Hopefully, with the macro I should be able to change the RGB value on all the workbooks. Thank you in advance for your support. Regards, rsrasc |
#2
|
|||
|
|||
Perhaps you can glean something from this.
Needs to be in a .xlsm in the same folder as those listed files. Needs list of files to work on starting in A1. Code:
Sub ChangeRGB() Dim wb As Workbook, ws As Worksheet Dim i As Long, cel As Range Dim fPath As String, arr As Variant fPath = ThisWorkbook.Path arr = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value Application.ScreenUpdating = False For i = LBound(arr) To UBound(arr) On Error Resume Next 'incase file doesn't exist Set wb = Workbooks.Open(Filename:=fPath & "\" & arr(i, 1) & ".xlsx") On Error GoTo 0 'reinstate error notification If Not wb Is Nothing Then With wb For Each ws In wb.Sheets For Each cel In Intersect(ws.Range("A:A"), ws.UsedRange) If cel.Interior.Color = RGB(0, 0, 0) Then cel.Resize(, 14).Interior.Color = RGB(192, 192, 192) End If Next cel Next ws wb.Close (True) End With Else MsgBox "Workbook not found:" & vbLf & vbLf & arr(i, 1) & ".xlsx" End If Set wb = Nothing Next i Application.ScreenUpdating = True End Sub |
#3
|
|||
|
|||
Hi NoSparks,
Thanks for the code. Working great! Much appreciated. Regards, rsrasc |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Macro Help Needed - White Background & PDF | PJinNH | Word VBA | 3 | 05-04-2017 04:58 PM |
***help*** macro needed | PokerBob | Excel | 8 | 03-18-2015 02:57 PM |
Macro Needed to bold specific lines and Macro to turn into CSV | anewteacher | Word VBA | 1 | 05-28-2014 03:59 PM |
Check box macro help needed | Aflac | Word | 4 | 03-24-2012 07:11 PM |
Callout macro needed - Help please | Peter Denly | Word VBA | 19 | 07-20-2011 03:30 AM |