VBA to condition format value base on font color in another sheet.

[hajiali]

Cell U5 using the following code to pull all agents if dates in range 'EMPLOYEE LIST'!$V$2:$BG$1000 is = to DATE($J$1,$G$1,$H$1 and dragged down to Cell U26

=IFERROR(INDEX('EMPLOYEE LIST'!$G$2:$G$1000,AGGREGATE(15,6,(ROW('EMPLOYEE LIST'!$G$2:$G$1000)-ROW('EMPLOYEE LIST'!G$2)+1)/(('EMPLOYEE LIST'!$V$2:$BG$1000=DATE($J$1,$G$1,$H$1))),ROWS($U$5:U5))),"")

'EMPLOYEE LIST'!$V$2:$BG$1000 will be manually format date to be "RED" when it is a PM Hours.
'EMPLOYEE LIST'!$G$2:$G$1000 = Names of employee

What im need the VBA to do is conditional format employee name U5:U26 to be "RED" based on if the date corresponding to the Name is RED.

ex.
sheet Employee List

JOHN	1/18	1/19
MIKE	1/19
KIM	1/19
JENN	1/19	1/20
JAMES	1/1	1/15	1/19

in BASE SHEET if DATE($J$1,$G$1,$H$1)=
J1= 2020 (year)
G1= 1 (month)
H1= 19 (Day)

U5:U26 results should be

JOHN

MIKE

KIM

JENN

JAMES

any help is greatly appreciated. let me know if you have any question.

Thanks

 

[CSmith]

See if this code helps

Create module and place this code there.

Public Function fFindColor(Target As Range, Color As Long) As Boolean
  Dim sCell As Range, ff As Boolean
  fFindColor = False
  For Each sCell In Target
    Debug.Print sCell.Address & " | " & sCell.Font.Color
    If sCell.Font.Color = Color Then
      fFindColor = True
      Exit For
    End If
  Next
End Function

Book1
	A	B
1	Red in B1:B5	Black
2	False	Black
3		Black
4		Black
5		Black
6	Red in B7:B9
7	True	Red
8		Red
9		Red
10		Red
11		Red
MrExcel Help

Cell Formulas
Range	Formula
A2	A2	=fFindColor(B1:B6, 255 )
A7	A7	=fFindColor(B7:B9, 255)

 

[hajiali]

See if this code helps

Create module and place this code there.

Public Function fFindColor(Target As Range, Color As Long) As Boolean
  Dim sCell As Range, ff As Boolean
  fFindColor = False
  For Each sCell In Target
    Debug.Print sCell.Address & " | " & sCell.Font.Color
    If sCell.Font.Color = Color Then
      fFindColor = True
      Exit For
    End If
  Next
End Function

Book1
	A	B
1	Red in B1:B5	Black
2	False	Black
3		Black
4		Black
5		Black
6	Red in B7:B9
7	True	Red
8		Red
9		Red
10		Red
11		Red
MrExcel Help

Cell Formulas
Range	Formula
A2	A2	=fFindColor(B1:B6, 255 )
A7	A7	=fFindColor(B7:B9, 255)

this could help however I will need to figure out the cell that has the date in red corresponding to the name. instead of range in =fFindColor(B1:B6, 255 ) I will need it to be the specific cell. I would think just do not know how to go about it.

 

[CSmith]

huh?

All you need to do is use the tool available 'Conditional Formatting'

Book1
	U	V	W	X
5	JOHN	1/18/2020	1/19/2020
6	MIKE	1/19/2020
7	KIM	1/19/2020
8	JENN	1/19/2020	1/20/2020
9	JAMES	1/1/2020	1/15/2020	1/19/2020
MrExcel Help

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
U5:U9	Expression	=fFindColor(V5:W5,255)	text	NO

 

[CSmith]

This code is required in a module or imported from text file with .bas extension is required for the above conditional formatting to work.

Attribute VB_Name = "modFindColor"
Public Function fFindColor(Target As Range, Color As Long) As Boolean
  Dim sCell As Range, ff As Boolean
  fFindColor = False
  For Each sCell In Target
    If sCell.Font.Color = Color Then
      fFindColor = True
      Exit For
    End If
  Next
End Function

 

[hajiali]

huh?

All you need to do is use the tool available 'Conditional Formatting'

the values change as the date in Base Sheet changes. I would like the outcome to auto Conditional Format based on the conditional formatting of the date in the other sheet. if you look at my example date 1/19 are in red for John and Kim so when i Change the date(2020,1,19) in sheet base sheet when it pulls the name John and Kim for the 19 it auto conditional formats those 2 in red.

another ex. in sheet Employee List

G.........	V	W	X.......
JOHN	1/20	1/19
MIKE	1/20
KIM	1/20
JENN	1/19	1/20
JAMES	1/1	1/20	1/19

in BASE SHEET if i change the DATE($J$1,$G$1,$H$1)=1/20/2020
J1= 2020 (year)
G1= 1 (month)
H1= 20 (Day)

U5:U26 results should be

JOHN

MIKE

KIM

JENN

JAMES

in this ex Mike, Jenn, and James should appear red BC the Date 1/20 in there rows are in red

hope this clarifies

 

[CSmith]

Ummm My example does EXACTLY THAT if you format those date colors RED, it will make the name RED

All you need to do is use the tool available 'Conditional Formatting'

Book1
	U	V	W	X
5	JOHN	1/18/2020	1/19/2020
6	MIKE	1/19/2020
7	KIM	1/19/2020
8	JENN	1/19/2020	1/20/2020
9	JAMES	1/1/2020	1/15/2020	1/19/2020
MrExcel Help

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
U5:U9	Expression	=fFindColor(V5:W5,255)	text	NO

 

[hajiali]

huh?

All you need to do is use the tool available 'Conditional Formatting'

Book1
	U	V	W	X
5	JOHN	1/18/2020	1/19/2020
6	MIKE	1/19/2020
7	KIM	1/19/2020
8	JENN	1/19/2020	1/20/2020
9	JAMES	1/1/2020	1/15/2020	1/19/2020
MrExcel Help

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
U5:U9	Expression	=fFindColor(V5:W5,255)	text	NO

Sorry misunderstood the the equation goes to conditioning formatting for those cells I will try that when I’m back on the computer.thanks

 

[hajiali]

Ummm My example does EXACTLY THAT if you format those date colors RED, it will make the name RED

So I tried your example on new sheet and it works. however, my Sheet Named "BASE SCH" cell U5 has the following formula

=IFERROR(INDEX('EMPLOYEE LIST'!$G$2:$G$1000,AGGREGATE(15,6,(ROW('EMPLOYEE LIST'!$G$2:$G$1000)-ROW('EMPLOYEE LIST'!G$2)+1)/(('EMPLOYEE LIST'!$V$2:$BG$1000=DATE($J$1,$G$1,$H$1))),ROWS($U$5:U5))),"")

and Dragged down to U26. This generates/pull all employees in Sheet "Employee List" if dates in range $V$2:$BG$1000= DATE($J$1,$G$1,$H$1) in sheet "BASE SCH" the names that need to condition formatting and dates are in different sheet.

and if im correct your example does it only if there on the same sheet?

 

[CSmith]

No, you are incorrect that formula goes into the conditional formatting not the cell contents. It will use the cell value of whatever RANGE you put into the function. Another page wont matter and it could even be a named range.

So I tried your example on new sheet and it works. however, my Sheet Named "BASE SCH" cell U5 has the following formula

=IFERROR(INDEX('EMPLOYEE LIST'!$G$2:$G$1000,AGGREGATE(15,6,(ROW('EMPLOYEE LIST'!$G$2:$G$1000)-ROW('EMPLOYEE LIST'!G$2)+1)/(('EMPLOYEE LIST'!$V$2:$BG$1000=DATE($J$1,$G$1,$H$1))),ROWS($U$5:U5))),"")

and Dragged down to U26. This generates/pull all employees in Sheet "Employee List" if dates in range $V$2:$BG$1000= DATE($J$1,$G$1,$H$1) in sheet "BASE SCH" the names that need to condition formatting and dates are in different sheet.

and if im correct your example does it only if there on the same sheet?