[Webinar] Streamline your web hosting managementRegister Today

x
Solved

# Perform multiplications on grey shaded cells using VBA

Posted on 2013-12-11
Medium Priority
329 Views
Dear Experts:

I would like to perform the following action using VBA on the current workbook

... Look for grey shaded cells (RGB 222, 222, 222) in all worksheets of the current workbook with exception of one named 'MasterSheet'
... If found perform the following multiplication:
C5 (Cell reference in worksheet 'MasterSheet') times the value found in that grey shaded cell
... Do the loop with this multiplication.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
Question by:Andreas Hermle
• 3
• 2
• 2

LVL 85

Expert Comment

ID: 39710970
Andreas,

Do you mean replace the value in the shaded cell with itself multiplied by C5 on the master sheet, or something else? "Do the loop with this multiplication." isn't really clear to me.

Regards,
Rory
0

LVL 43

Expert Comment

ID: 39710978
Sub mulgrey()
Dim cel As Range
For Each cel In ActiveSheet.UsedRange.Cells
If cel.Interior.Color = RGB(222, 222, 222) Then
cel.Value = Sheets("Mastersheet").Range("C5") * cel.Value
End If
Next cel
End Sub
0

Author Comment

ID: 39710998
Hi Roy,

ok, the requirements were not clear, I have to admit.

It is: the value in the grey shaded cell times C5 (MasterSheet)
0

Author Comment

ID: 39711000
Hi ssaqibh:

we are almost there, thank you very much. The multiplication is only performed on the active sheet, but there are grey shaded cells in all worksheets. The multiplication is not to be performed in the 'MasterSheet' worksheet.

Thank you, Regards, Andreas
0

LVL 85

Expert Comment

ID: 39711020
I'll leave it to Saqibh then as he's pretty much done it already. :)
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 2000 total points
ID: 39711032
Sorry, I missed the "All worksheets" part.

Sub mulgrey()
Dim cel As Range
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Mastersheet" Then
For Each cel In ws.UsedRange.Cells
If cel.Interior.Color = RGB(222, 222, 222) Then
cel.Value = Sheets("Mastersheet").Range("C5") * cel.Value
End If
Next cel
End If
Next ws
End Sub
0

Author Closing Comment

ID: 39711107
ssaqibh:

great this did the trick. Thank you very much for your professional help.

Regards, Andreas
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
###### Suggested Courses
Course of the Month9 days, 8 hours left to enroll