Change color based if cell another sheet are not empty

[makis1023]

Hello,

I am trying to make a cell change color if a range of cells on another worksheet are not empty

ie cell on worksheet TEST1 that need to change color is E3 and have to check if a range of (lets say B2:C10 cells on Worksheet TEST2 are not empty.

Can this be done with conditional formatting of need VBA?

Cheers

 

[FormR]

Can this be done with conditional formatting

Hi, yes it can - with the "use a formula" method you could try this.

=COUNTA('TEST2'!B2:C10)

 

[makis1023]

FormR , Yes this works perfectly if one cell of the range ('TEST2'!B2:C10) is filled. The need is, all the cells of TEST2 (B2:C10) are filled, all the range, not just one.

Thank you

 

[FormR]

The need is, all the cells of TEST2 (B2:C10) are filled, all the range

Hi, in that case, how about.

=COUNTA('TEST2'!B2:C10)=18

 

[makis1023]

Hello again. I need something more difficult. As you can see I have these 2 sheets. The one (calendar) has one calendar (daily) and when I double click a day it creates a new sheet based on date and data from sheet test19.

test1000.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2		Year:	2021	Month:	June
3
4		Monday	Tuesday	Wendsday	Thursday	Friday	Saturday	Sunday
5		31	1	2	3	4	5	6
6		7	8	9	10	11	12	13
7		14	15	16	17	18	19	20
8		21	22	23	24	25	26	27
9		28	29	30	1	2	3	4
10		5	6	7	8	9	10	11
11
12
13
14
15
16
17
18
Calendar

Cell Formulas
Range	Formula
B5	B5	=DATE($C$2,MATCH($E$2,{"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"},0),1)-WEEKDAY(DATE($C$2,MATCH($E$2,{"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"},0),1),2)+1
C5:H10	C5	=B5+1
B6:B10	B6	=B5+7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:H10	Expression	=MONTH(B5)<>MONTH($B$6)	text	NO
B5:H10	Expression	=B5=TODAY()	text	NO
B5:H10	Expression	=OR(B5=INT(INDEX(Data;0;1)))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C2	List	2021;2022
E2	List	January; February; March; April; May; June; July; August; September; October; November; December

test1000.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Α (10:00- 18:00)
2	test19	total 1	total 2	total 3	total 4	total 5	total 6
3	emp1					0,00 €
4	emp2				0,00 €	0,00 €
5	emp3				0,00 €	0,00 €
6	emp4	0,00 €	0,00 €	0,00 €	0,00 €	0,00 €
7
8	Β (18:00- 00:00)
9		total 1	total 2	total 3	total 4	total 5	total 6
10	emp1					0,00 €
11	emp2				0,00 €	0,00 €
12	emp3				0,00 €	0,00 €
13	emp4	0,00 €	0,00 €	0,00 €	0,00 €	0,00 €
14
15
16
17	B2:F6
18	B10:F13
test19

Cell Formulas
Range	Formula
A2	A2	=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
F3:F4	F3	=SUM(B3-C3-D3-E3)
E11:E12,E4:E5	E4	=SUM(B4*0.8%)
B6,D13:F13,B13,D6:F6	B6	=SUM(B3:B5)
C6,C13	C6	=(G6/1.05)*5%
F10:F11	F10	=SUM(B10-C10*D10-E10)

I need to check if the range of B2:F6 & B10:F13 at sheet test19 are filled, then change background color of that day at calendar to red. Is that possible?
As you can see I use already conditional formatting for today() at calendar to be yellow

Thanks!

 

[FormR]

I need to check if the range of B2:F6 & B10:F13 at sheet test19 are filled, then change background color of that day at calendar to red

Hi, did you try:

=COUNTA(test19!$B$2:$F$6,test19!$B$10:$F$13)=45

 

[makis1023]

Oh, my mistake
I need to check the newly created sheet , that the name of the sheet is the day of the calendar i.e.. 30-06-2021
The table sheet is the template for the newly created.

The code for the new sheet is

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim oWs As Worksheet
    Dim ShtName As String
    
    ''///will only run on calendar cells and one cell selected
    If Intersect(Target, Range("B5:N10")) Is Nothing Or Target.CountLarge > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
    If Target.Value = Date Then
        ShtName = Format(Date, "dd-mm-yyyy")
        
        If WksExists(ShtName) Then
           If Sheets(ShtName).Visible = xlSheetHidden Then
           Sheets(ShtName).Visible = xlSheetVisible
           Else: Sheets(ShtName).Visible = xlSheetHidden
           End If
            Else:
            With Sheets("test19")
            .Visible = True
            .Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = ShtName
            ActiveSheet.Tab.Color = vbWhite
'           .Visible = xlSheetHidden
'            Sheets(ShtName).Visible = xlSheetHidden
        End With
    End If
  Else: On Error Resume Next
    Set oWs = Sheets(Format(Target.Value, "dd-mm-yyyy"))
    If oWs.Visible = xlSheetVisible Then
    oWs.Visible = xlSheetHidden
    Else: oWs.Visible = xlSheetVisible
    End If
    On Error GoTo 0
End If

End Sub

Thank you

 

[makis1023]

@FormR any thoughts ? somehow to do that?

 

[FormR]

Hi, I'm a bit lost to be honest, what does the latest post have to do with the original question about conditional formatting?

 

[makis1023]

I never speak about conditional formatting. English is not my native language, so apologies if I didn't explain it correct.
My last question is the correct one. post no5 Change color based if cell another sheet are not empty
I am sorry for any inconvenience!