Solved

# Perform multiplications on grey shaded cells using VBA

Posted on 2013-12-11
284 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:AndreasHermle
• 3
• 2
• 2

LVL 85

Expert Comment

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

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

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

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

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

LVL 43

Accepted Solution

Saqib Husain, Syed earned 500 total points
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

ssaqibh:

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

Regards, Andreas
0

## Featured Post

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.