Counting Colored Cells in particular row in whole workbook

[halfsparrow]

DSC SAMPLE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
4										March 12, 2021
5		Floor to			RISER #				RISER #
6		Floor		Drain	S01				S02
7	Floor	Height		Size
8	No.				Total Units =		18		Total Units =		18
9		MM	INCH	MM	HWS	CHWS	HWR	CHWR	HWS	CHWS	HWR	CHWR
10	L1
11	L2
12	L3
13	L4	3600	141.75	32	20CST	20CST	20CRT	20CRT
14	L5	3600	141.75	32	40	50	20	20>25	20CST	20CST	20CRT	20CRT
15	L6	3000	118.25	32	40	50	20	25	40	50	20	20
16	L7	3000	118.25	32	40>32	50	20	25	40>32	50	20	20>25
17	L8	3000	118.25	32	32	50	20>25	25	32	50	20	25
18	L9	3000	118.25	32	32	50	25	25>32	32	50	20	25
19	L10	3000	118.25	32	32	50>40	25	32	32	50	20>25	25
20	L11	3000	118.25	32	32	40	25	32	32	50	25	25>32
21	L12	3000	118.25	32	32	40	25	32 *	32	50>40	25	32 *
22	L13	3000	118.25	32	32	40	25 *	32	32	40	25 *	32
23	L14	3000	118.25	32	32>25	40 *	25	32>40	32	40 *	25	32
24	L15	3600	141.75	32	25 *	40>32	25>32	40	* 32>25	40	25	32
25	L16	3000	118.25	32					25	40>32	25>32	32>40
26	L17	3000	118.25	32	25	32	32	40	25	32	32	40
27	L18	3000	118.25	32	25	32	32	40>50	25	32	32	40>50
28	L19	3000	118.25	32	25>20	32>25	32	50	25>20	32>25	32	50
29	L20	3000	118.25	32	20	25	32>40	50	20	25	32>40	50
30	L21	3000	118.25	32	20	25>20	40	50	20	25>20	40	50
31	L22	3000	118.25	32	20CS90	20CS90	40	50	20CS90	20CS90	40	50
32
1

Cell Formulas
Range	Formula
J4	J4	='INPUT and Summary'!B4
G8,K8	G8	=COUNTA(F10:F46)*COUNTA(E6:H7)
C10:C32	C10	=CEILING(B10*0.0393701, 0.25)

Hello experts

I need help of a very small thing

so basically what i need help with is:

There is a userForm1 that will allow user to open the excel file they want and as soon as the open another excel file UserForm2 pops up

userform2 has a text field that allows user to enter a value.

what i want is if the user entered 7 in that value my application should search for 7 in Column A and select the whole row that has 7 and count the light blue colored cell in that particular row



For example, the excel file i have attached has only 1 sheet but the things i will be working on will have more than one sheet.

So for instance a user entered L15 in the textbox, what i want is to find the L15 in Column A and select that entire row and tell me that it has 2 light blue colored cells (i just want for the light blue and not the other colors)

if the file has more than one sheet than it need to search L28 on every sheet and give out total number of light blue colored cells in total. (like sheet 1 has 2 light blue colored cell, sheet 2 has 3, sheet 3 has 5 so it should show me L28 has 10 light blue colored cells)



RGB for that color is : RGB(189, 215, 238)



my code for userform1

name of textbox in userform2 is TextBox1

Uploaded at: Counting Colored Cells in particular row in whole workbook - OzGrid Free Excel/VBA Help Forum

 

[mart37]

Function CountColoredCells()
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim rng1 As Range, rng2 As Range, c As Range
    Dim lr As Long
    Dim f as Variant
    
    CountColoredCells = 0
    Set wb = ActiveWorkbook
    For Each sh In wb.Worksheets
        lr = sh.Range("A" & Rows.Count).End(xlUp).Row
        Set rng1 = sh.Range("A1:A" & lr)
        f = Application.Match(UserForm1.TextBox1, rng1, 0)
        If Not IsError(f) Then
            lc = sh.Cells(f, Columns.Count).End(xlToLeft).Column
            Set rng2 = sh.Range(sh.Cells(f, 1), sh.Cells(f, lc))
            For Each c In rng2
                If c.Interior.Color = RGB(189, 215, 238) Then CountColoredCells = CountColoredCells + 1
            Next
        End If
    Next
End Function

 

[halfsparrow]

It shows ariable not defined for lc

 

[halfsparrow]

Thankyou i made some changes and it worked