Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-12-2019, 07:26 AM
rsrasc rsrasc is offline Windows 10 Office 2013
Competent Performer
 
Join Date: Mar 2014
Location: Germany
Posts: 135
rsrasc is on a distinguished road
Default 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
Attached Files
File Type: xlsx 100-FY 19-Monthly Report.xlsx (58.6 KB, 1 views)
Reply With Quote
  #2  
Old 02-12-2019, 01:11 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 670
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

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
Hope that helps.
Reply With Quote
  #3  
Old 02-12-2019, 03:42 PM
rsrasc rsrasc is offline Windows 10 Office 2013
Competent Performer
 
Join Date: Mar 2014
Location: Germany
Posts: 135
rsrasc is on a distinguished road
Default

Hi NoSparks,

Thanks for the code. Working great!

Much appreciated.

Regards,
rsrasc
Reply With Quote
Reply

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


All times are GMT -7. The time now is 01:34 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft