Calculation of values based on the color of cells in Excel VBA
Calculation of values based on the color of cells in Excel VBA
The code shows a simple average calculation based on the values in the defined cells. Those cells are highlighted in three colors. The aim is to take the values into the calcuation which cell color is e.g. green. I know the "if" command is needed, I just dont know where excatly to put it in:
Dim wb As Workbook, wq As Object
Dim ws As Worksheet, datdatum
Dim cell As Range, cell2 As Range, col As Long
ws.Range("H104:U104").Formula = "= Average(H34,H39,H68,H71,H83)"
The colors are pre-conditionally set. What does UDF stand for? Thank you for your effort.
– Yavuz Topal
Sep 9 '18 at 19:22
Are the 'colors' manually set or conditional formatted?
– Jeeped
Sep 9 '18 at 19:23
They are conditional formatted.
– Yavuz Topal
Sep 9 '18 at 19:43
1 Answer
1
I'll assume that entire rows are not green and that each column needs to be looked at independently.
Loop through each column from H to U. Loop through each cell in each column. Build a union of the cells that are green and average the union. Move on to the next column.
There is no point in building a formula for each column since any change would require rerunning the sub procedure. These will work on both manually set and conditional formatted cell colors.
.DisplayFormat does not work within a User Defined Function.
dim c as long, r as long, rng as range
with worksheets("sheet1")
for c =8 to 21
for r=2 to 103
if .cells(r, c).displayformat.interior.color = vbgreen then
if rng is nothing then
set rng = .cells(r, c)
else
set rng = union(rng, .cells(r, c))
end if
end if
next r
if not rng is nothing then _
.cells(104, c) = application.average(rng)
'alternate
'if not rng is nothing then _
'.cells(104, c).formula = "=average(" & rng.address(0,0) & ")"
next c
end with
Alternate,
dim c as long
with worksheets("sheet1")
if .autofiltermode then .autofiltermode = false
for c =8 to 21
with .range(.cells(1, c), .cells(103, c))
.AutoFilter Field:=1, Criteria1:=vbgreen, Operator:=xlFilterCellColor
.cells(104, c) = application.subtotal(101, .cells)
.AutoFilter
end with
next c
end with
I have just uploaded an image for clarity. Thank you for your code, I will tried it out tomorrow. Much appreciated
– Yavuz Topal
Sep 9 '18 at 20:01
You are going to have to determine exactly what shade of green you are using in the CFR. Could be vbGreen, could be RGB(146, 208 , 80), could be other.
– Jeeped
Sep 9 '18 at 20:21
I have used "vbGreen". Thx
– Yavuz Topal
Sep 9 '18 at 20:28
Hi Jeeped, the code worked perfectly. However, i would like to have the formula included in the cell not only the calcualted value. I tried this: .Cells(104, c).Formula = "= Average(rng)", but didnt work much
– Yavuz Topal
Sep 10 '18 at 11:13
You cannot use a vba range in a worksheet formula directly. You can however, use its address.
– Jeeped
Sep 10 '18 at 11:19
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
So you would need to examine every cell in every column from H:U independently. Probably better to use a UDF. Are the 'colors' manually set or conditional formatted?
– Jeeped
Sep 9 '18 at 19:14