conditional formatting question

biglb79 · Feb 21, 2024

I'm hoping someone can help me with this. I want to have a row highlighted yellow in columns A:T if the patient name in column I and MRAdmit number in column H appear on the prior period unbilled tab

the prior period unbilled tab has data in columns A:AF and the patient name appears in column E while the MRAdmit number appears in column A

etaf · Feb 21, 2024

something like
as a rule
countifs('prior period unbilled'!$E$2:$E$100000, $I2, 'prior period unbilled'!$A$2:$A$100000, $H2)>0 - change the rows 100000 to whatever the maximum possible rows are
I prefer not to use full column reference for performance BUT you could use that
countifs('prior period unbilled'!$E:$E, $I2, 'prior period unbilled'!$A:$A, $H2)>0

or 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A2:T10000 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=countifs('prior period unbilled'!$E$2:$E$100000, $I2, 'prior period unbilled'!$A$2:$A$100000, $H2)>0

Format [Number, Font, Border, Fill] - FILL YELLOW
choose the format you would like to apply when the condition is true
OK >> OK
change the name 'prior period unbilled' to whatever the name of the sheet is

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

dreid1011 · Feb 21, 2024

Maybe this:

Change "Sheet3" to your sheet name.

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

MRAdmit

Patient

2

12345

Joe

3

45678

Jane

4

12345

Joe

Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:T10	Expression	=AND($I2<>"",MATCH($H2&$I2,Sheet3!$A$2:$A$10&Sheet3!$E$2:$E$10,0))	text	NO

biglb79 · Feb 21, 2024

etaf said:
something like
as a rule
countifs('prior period unbilled'!$E$2:$E$100000, $I2, 'prior period unbilled'!$A$2:$A$100000, $H2)>0 - change the rows 100000 to whatever the maximum possible rows are
I prefer not to use full column reference for performance BUT you could use that
countifs('prior period unbilled'!$E:$E, $I2, 'prior period unbilled'!$A:$A, $H2)>0

or 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A2:T10000 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=countifs('prior period unbilled'!$E$2:$E$100000, $I2, 'prior period unbilled'!$A$2:$A$100000, $H2)>0

Format [Number, Font, Border, Fill] - FILL YELLOW
choose the format you would like to apply when the condition is true
OK >> OK
change the name 'prior period unbilled' to whatever the name of the sheet is

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

I think I figured out the XL2BB

Book6

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

3

Transaction

Ledger

Debit G/L Acct

Credit G/L Acct

G/L Date

Trans ID

Service Date

MRAdmit

Patient Name

Admit Date

Discharge Date

Program

Payor Name

PayorSite Name

Finclass Code

Service Code

Description

Gross Amount

Net Amount

System

4

CHG

STANDARD

130-07-000-05

300-07-301-05

1/3/2024

1198045

1/3/2024

5501086-1

Soprano, Tony

9/8/2023

1/29/2024

MH IOP

MGD MCAID

no

5

CHG

STANDARD

130-07-000-05

300-07-301-05

1/3/2024

1198044

1/3/2024

5501267-4

Soprano, Carmela

9/8/2023

1/29/2024

MH IOP

MGD MCAID

no

6

CHG

STANDARD

130-07-000-05

300-07-301-05

1/3/2024

1198043

1/3/2024

5501550-1

Sorpano, AJ

9/20/2023

1/19/2024

MH IOP

MGD MCAID

no

7

CHG

STANDARD

130-07-000-05

300-07-301-05

1/3/2024

1198042

1/3/2024

5501883-2

Soprano, Meadow

11/3/2023

1/18/2024

MH IOP

MGD MCAID

no

8

CHG

STANDARD

130-01-000-05

300-01-301-05

1/3/2024

1198041

1/3/2024

5500362-1

Soprano, Janice

12/7/2023

MH IOP

MGD MCARE

no

9

CHG

STANDARD

130-07-000-05

300-07-301-05

1/3/2024

1198040

1/3/2024

5500394-3

Maltisonti, Christopher

12/18/2023

MH IOP

MGD MCAID

no

10

CHG

STANDARD

130-04-000-05

300-04-301-05

1/5/2024

1198039

1/5/2024

5501194-2

Walnuts, Paulie

12/11/2023

MH IOP

BCBS

no

11

CHG

STANDARD

130-07-000-05

300-07-301-05

1/5/2024

1198038

1/5/2024

5500840-3

Sack, Johnny

9/8/2023

1/29/2024

MH IOP

MGD MCAID

no

12

CHG

STANDARD

130-07-000-05

300-07-301-05

1/5/2024

1198037

1/5/2024

5501194-2

Dante, Silvio

9/20/2023

1/19/2024

MH IOP

MGD MCAID

no

13

CHG

STANDARD

130-07-000-05

300-07-301-05

1/5/2024

1198036

1/5/2024

5500840-3

Bucco, Artie

11/3/2023

1/18/2024

MH IOP

MGD MCAID

no

14

CHG

STANDARD

130-01-000-05

300-01-301-05

1/5/2024

1198035

1/5/2024

5501714-1

Melfie, Doctor

12/7/2023

MH IOP

MGD MCARE

no

15

CHG

STANDARD

130-07-000-05

300-07-301-05

1/5/2024

1198034

1/5/2024

5500218-3

La Cerva, Adriana

9/8/2023

1/29/2024

MH IOP

MGD MCAID

no

16

CHG

STANDARD

130-01-000-05

300-01-301-05

1/5/2024

1198033

1/5/2024

5502228-2

Leotardo, Phil

12/7/2023

MH IOP

MGD MCARE

no

17

CHG

STANDARD

130-07-000-05

300-07-301-05

1/8/2024

1198032

1/8/2024

5500218-3

Giunta, Furio

9/8/2023

1/29/2024

MH IOP

MGD MCAID

no

18

CHG

STANDARD

130-07-000-05

300-07-301-05

1/8/2024

1198031

1/8/2024

5501230-3

Soprano, Livia

9/20/2023

1/19/2024

MH IOP

MGD MCAID

no

19

CHG

STANDARD

130-01-000-05

300-01-301-05

1/8/2024

1198030

1/8/2024

5502228-2

Ciferetto, Ralph

12/7/2023

MH IOP

MGD MCARE

no

20

CHG

STANDARD

130-07-000-05

300-07-301-05

1/8/2024

1198029

1/8/2024

5500794-4

Bacalla, Bobby

12/18/2023

MH IOP

MGD MCAID

no

SVH ChargeGL

biglb79 · Feb 21, 2024

biglb79 said:
I think I figured out the XL2BB

Book6
A B C D E F G H I J K L M N O P Q R S T
3 Transaction Ledger Debit G/L Acct Credit G/L Acct G/L Date Trans ID Service Date MRAdmit Patient Name Admit Date Discharge Date Program Payor Name PayorSite Name Finclass Code Service Code Description Gross Amount Net Amount System
4 CHG STANDARD 130-07-000-05 300-07-301-05 1/3/2024 1198045 1/3/2024 5501086-1 Soprano, Tony 9/8/2023 1/29/2024 MH IOP MGD MCAID no
5 CHG STANDARD 130-07-000-05 300-07-301-05 1/3/2024 1198044 1/3/2024 5501267-4 Soprano, Carmela 9/8/2023 1/29/2024 MH IOP MGD MCAID no
6 CHG STANDARD 130-07-000-05 300-07-301-05 1/3/2024 1198043 1/3/2024 5501550-1 Sorpano, AJ 9/20/2023 1/19/2024 MH IOP MGD MCAID no
7 CHG STANDARD 130-07-000-05 300-07-301-05 1/3/2024 1198042 1/3/2024 5501883-2 Soprano, Meadow 11/3/2023 1/18/2024 MH IOP MGD MCAID no
8 CHG STANDARD 130-01-000-05 300-01-301-05 1/3/2024 1198041 1/3/2024 5500362-1 Soprano, Janice 12/7/2023 MH IOP MGD MCARE no
9 CHG STANDARD 130-07-000-05 300-07-301-05 1/3/2024 1198040 1/3/2024 5500394-3 Maltisonti, Christopher 12/18/2023 MH IOP MGD MCAID no
10 CHG STANDARD 130-04-000-05 300-04-301-05 1/5/2024 1198039 1/5/2024 5501194-2 Walnuts, Paulie 12/11/2023 MH IOP BCBS no
11 CHG STANDARD 130-07-000-05 300-07-301-05 1/5/2024 1198038 1/5/2024 5500840-3 Sack, Johnny 9/8/2023 1/29/2024 MH IOP MGD MCAID no
12 CHG STANDARD 130-07-000-05 300-07-301-05 1/5/2024 1198037 1/5/2024 5501194-2 Dante, Silvio 9/20/2023 1/19/2024 MH IOP MGD MCAID no
13 CHG STANDARD 130-07-000-05 300-07-301-05 1/5/2024 1198036 1/5/2024 5500840-3 Bucco, Artie 11/3/2023 1/18/2024 MH IOP MGD MCAID no
14 CHG STANDARD 130-01-000-05 300-01-301-05 1/5/2024 1198035 1/5/2024 5501714-1 Melfie, Doctor 12/7/2023 MH IOP MGD MCARE no
15 CHG STANDARD 130-07-000-05 300-07-301-05 1/5/2024 1198034 1/5/2024 5500218-3 La Cerva, Adriana 9/8/2023 1/29/2024 MH IOP MGD MCAID no
16 CHG STANDARD 130-01-000-05 300-01-301-05 1/5/2024 1198033 1/5/2024 5502228-2 Leotardo, Phil 12/7/2023 MH IOP MGD MCARE no
17 CHG STANDARD 130-07-000-05 300-07-301-05 1/8/2024 1198032 1/8/2024 5500218-3 Giunta, Furio 9/8/2023 1/29/2024 MH IOP MGD MCAID no
18 CHG STANDARD 130-07-000-05 300-07-301-05 1/8/2024 1198031 1/8/2024 5501230-3 Soprano, Livia 9/20/2023 1/19/2024 MH IOP MGD MCAID no
19 CHG STANDARD 130-01-000-05 300-01-301-05 1/8/2024 1198030 1/8/2024 5502228-2 Ciferetto, Ralph 12/7/2023 MH IOP MGD MCARE no
20 CHG STANDARD 130-07-000-05 300-07-301-05 1/8/2024 1198029 1/8/2024 5500794-4 Bacalla, Bobby 12/18/2023 MH IOP MGD MCAID no
SVH ChargeGL

and here is the 2nd tab

Book6

A

B

C

D

E

F

G

H

I

K

L

M

N

O

P

R

X

Y

AC

AD

AE

AF

1

MR-Admit#

Admit Date

Discharge Date

Discharge Status

Patient Name

G/L Date

Service Date

Service Code

Bill Date

Charge Description

Program

Department

FinClass

Payor

Location

Gross Rate

Net Rate

Extended Net

C/A Reserve Amt

Posted W/O

W/O Reserve Amt

2

5501086-1

6/1/2023

7/14/2023

1

Soprano, Tony

7/13/2023

MGD MCAID

3

5501267-4

10/24/2023

11/13/2023

1

Soprano, Carmela

10/27/2023

MCAID

4

5501550-1

8/29/2023

9/15/2023

1

Sorpano, AJ

9/11/2023

MGD MCAID

5

5501883-2

12/17/2023

Soprano, Meadow

12/31/2023

MGD MCAID

6

5500362-1

12/11/2022

12/19/2022

1

Soprano, Janice

12/18/2022

MGD MCAID

7

5500394-3

12/15/2023

12/26/2023

1

Maltisonti, Christopher

12/18/2023

MGD MCAID

8

5501194-2

12/6/2023

12/14/2023

1

Walnuts, Paulie

12/13/2023

MGD MCAID

9

5500840-3

12/20/2023

12/28/2023

1

Sack, Johnny

12/27/2023

MGD MCAID

10

5501194-2

12/6/2023

12/14/2023

1

Dante, Silvio

12/6/2023

MGD MCAID

11

5500840-3

12/20/2023

12/28/2023

1

Bucco, Artie

12/26/2023

MGD MCAID

12

5501714-1

9/27/2023

10/7/2023

1

Melfie, Doctor

9/27/2023

MGD MCAID

13

5501772-1

10/11/2023

10/19/2023

1

La Cerva, Adriana

10/12/2023

MGD MCARE

14

5502343-1

12/19/2023

12/24/2023

1

Leotardo, Phil

12/21/2023

COMM

15

5500218-3

9/8/2023

Giunta, Furio

12/6/2023

MGD MCAID

16

5502361-1

12/24/2023

12/29/2023

1

Soprano, Livia

12/24/2023

SELF-PAY

17

5502361-1

12/24/2023

12/29/2023

1

Ciferetto, Ralph

12/25/2023

SELF-PAY

18

5502361-1

12/24/2023

12/29/2023

1

Bacalla, Bobby

12/26/2023

SELF-PAY

prior period unbilled

etaf · Feb 21, 2024

the formula i posted should work in conditional formatting

cond-frmt-ETAF.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

Transaction

Ledger

Debit G/L Acct

Credit G/L Acct

G/L Date

Trans ID

Service Date

MRAdmit

Patient Name

Admit Date

Discharge Date

Program

Payor Name

PayorSite Name

Finclass Code

Service Code

Description

Gross Amount

Net Amount

System

2

CHG

STANDARD

130-07-000-05

300-07-301-05

45294

1198045

45294

5501086-1

Soprano, Tony

45177

45320

MH IOP

MGD MCAID

no

3

CHG

STANDARD

130-07-000-05

300-07-301-05

45294

1198044

45294

5501267-4

Soprano, Carmela

45177

45320

MH IOP

MGD MCAID

no

4

CHG

STANDARD

130-07-000-05

300-07-301-05

45294

1198043

45294

5501550-1

Sorpano, AJ

45189

45310

MH IOP

MGD MCAID

no

5

CHG

STANDARD

130-07-000-05

300-07-301-05

45294

1198042

45294

5501883-2

Soprano, Meadow

45233

45309

MH IOP

MGD MCAID

no

6

CHG

STANDARD

130-01-000-05

300-01-301-05

45294

1198041

45294

5500362-1

Soprano, Janice

45267

MH IOP

MGD MCARE

no

7

CHG

STANDARD

130-07-000-05

300-07-301-05

45294

1198040

45294

5500394-3

Maltisonti, Christopher

45278

MH IOP

MGD MCAID

no

8

CHG

STANDARD

130-04-000-05

300-04-301-05

45296

1198039

45296

5501194-2

Walnuts, Paulie

45271

MH IOP

BCBS

no

9

CHG

STANDARD

130-07-000-05

300-07-301-05

45296

1198038

45296

5500840-3

Sack, Johnny

45177

45320

MH IOP

MGD MCAID

no

10

CHG

STANDARD

130-07-000-05

300-07-301-05

45296

1198037

45296

5501194-2

Dante, Silvio

45189

45310

MH IOP

MGD MCAID

no

11

CHG

STANDARD

130-07-000-05

300-07-301-05

45296

1198036

45296

5500840-3

Bucco, Artie

45233

45309

MH IOP

MGD MCAID

no

12

CHG

STANDARD

130-01-000-05

300-01-301-05

45296

1198035

45296

5501714-1

Melfie, Doctor

45267

MH IOP

MGD MCARE

no

13

CHG

STANDARD

130-07-000-05

300-07-301-05

45296

1198034

45296

5500218-3

La Cerva, Adriana

45177

45320

MH IOP

MGD MCAID

no

14

CHG

STANDARD

130-01-000-05

300-01-301-05

45296

1198033

45296

5502228-2

Leotardo, Phil

45267

MH IOP

MGD MCARE

no

15

CHG

STANDARD

130-07-000-05

300-07-301-05

45299

1198032

45299

5500218-3

Giunta, Furio

45177

45320

MH IOP

MGD MCAID

no

16

CHG

STANDARD

130-07-000-05

300-07-301-05

45299

1198031

45299

5501230-3

Soprano, Livia

45189

45310

MH IOP

MGD MCAID

no

17

CHG

STANDARD

130-01-000-05

300-01-301-05

45299

1198030

45299

5502228-2

Ciferetto, Ralph

45267

MH IOP

MGD MCARE

no

18

CHG

STANDARD

130-07-000-05

300-07-301-05

45299

1198029

45299

5500794-4

Bacalla, Bobby

45278

MH IOP

MGD MCAID

no

19

20

21

22

23

24

Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:T100	Expression	=COUNTIFS('Prior Period UnBilled'!$E$2:$E$100000, $I2, 'Prior Period UnBilled'!$A$2:$A$100000, $H2)>0	text	NO

based on the 2nd sheet

biglb79 · Feb 21, 2024

etaf said:
the formula i posted should work in conditional formatting

cond-frmt-ETAF.xlsx
A B C D E F G H I J K L M N O P Q R S T
1 Transaction Ledger Debit G/L Acct Credit G/L Acct G/L Date Trans ID Service Date MRAdmit Patient Name Admit Date Discharge Date Program Payor Name PayorSite Name Finclass Code Service Code Description Gross Amount Net Amount System
2 CHG STANDARD 130-07-000-05 300-07-301-05 45294 1198045 45294 5501086-1 Soprano, Tony 45177 45320 MH IOP MGD MCAID no
3 CHG STANDARD 130-07-000-05 300-07-301-05 45294 1198044 45294 5501267-4 Soprano, Carmela 45177 45320 MH IOP MGD MCAID no
4 CHG STANDARD 130-07-000-05 300-07-301-05 45294 1198043 45294 5501550-1 Sorpano, AJ 45189 45310 MH IOP MGD MCAID no
5 CHG STANDARD 130-07-000-05 300-07-301-05 45294 1198042 45294 5501883-2 Soprano, Meadow 45233 45309 MH IOP MGD MCAID no
6 CHG STANDARD 130-01-000-05 300-01-301-05 45294 1198041 45294 5500362-1 Soprano, Janice 45267 MH IOP MGD MCARE no
7 CHG STANDARD 130-07-000-05 300-07-301-05 45294 1198040 45294 5500394-3 Maltisonti, Christopher 45278 MH IOP MGD MCAID no
8 CHG STANDARD 130-04-000-05 300-04-301-05 45296 1198039 45296 5501194-2 Walnuts, Paulie 45271 MH IOP BCBS no
9 CHG STANDARD 130-07-000-05 300-07-301-05 45296 1198038 45296 5500840-3 Sack, Johnny 45177 45320 MH IOP MGD MCAID no
10 CHG STANDARD 130-07-000-05 300-07-301-05 45296 1198037 45296 5501194-2 Dante, Silvio 45189 45310 MH IOP MGD MCAID no
11 CHG STANDARD 130-07-000-05 300-07-301-05 45296 1198036 45296 5500840-3 Bucco, Artie 45233 45309 MH IOP MGD MCAID no
12 CHG STANDARD 130-01-000-05 300-01-301-05 45296 1198035 45296 5501714-1 Melfie, Doctor 45267 MH IOP MGD MCARE no
13 CHG STANDARD 130-07-000-05 300-07-301-05 45296 1198034 45296 5500218-3 La Cerva, Adriana 45177 45320 MH IOP MGD MCAID no
14 CHG STANDARD 130-01-000-05 300-01-301-05 45296 1198033 45296 5502228-2 Leotardo, Phil 45267 MH IOP MGD MCARE no
15 CHG STANDARD 130-07-000-05 300-07-301-05 45299 1198032 45299 5500218-3 Giunta, Furio 45177 45320 MH IOP MGD MCAID no
16 CHG STANDARD 130-07-000-05 300-07-301-05 45299 1198031 45299 5501230-3 Soprano, Livia 45189 45310 MH IOP MGD MCAID no
17 CHG STANDARD 130-01-000-05 300-01-301-05 45299 1198030 45299 5502228-2 Ciferetto, Ralph 45267 MH IOP MGD MCARE no
18 CHG STANDARD 130-07-000-05 300-07-301-05 45299 1198029 45299 5500794-4 Bacalla, Bobby 45278 MH IOP MGD MCAID no
19
20
21
22
23
24
Sheet2
Cells with Conditional Formatting
Cell Condition Cell Format Stop If True
A2:T100 Expression =COUNTIFS('Prior Period UnBilled'!$E$2:$E$100000, $I2, 'Prior Period UnBilled'!$A$2:$A$100000, $H2)>0 text NO

based on the 2nd sheet

for some reason I can't get it to work. could it be because of the format for the name and MRAdmit# being a general format?

etaf · Feb 21, 2024

no , if same in both

what happens - the formula needs to start with th esame row number as the selection

Highlight applicable range >>
A2:T10000 - Change, reduce or extend the rows to meet your data range of rows

so as you have selected row 2 here , then the bit its matching with on needs to be row 2
the other sheet can be anything for that range
so it could be on the other sheet starting at row 10 - doesnt matter so long as its the correct range
=countifs('prior period unbilled'!$E$10:$E$100000, $I2, 'prior period unbilled'!$A$10:$A$100000, $H2)>0

the orange bits need to be the same row number as the selection for conditional formatting
Also the $ are important on the columns as well

on a drop box link for a couple of days

cond-frmt-ETAF.xlsx

Shared with Dropbox

www.dropbox.com

biglb79 · Feb 21, 2024

am I typing this incorrectly? I did conditional formatting use a formula for a rule after highlighting A4:T4 (will copy down once I have it working on the top row)

="""countifs('prior period unbilled'!$E:$E, $I4, 'prior period unbilled'!$A:$A, $H4)>0"

etaf · Feb 21, 2024

(will copy down once I have it working on the top row)

ok - makes no difference though and its NOT copied

="""countifs('prior period unbilled'!$E:$E, $I4, 'prior period unbilled'!$A:$A, $H4)>0"
why the ""
=countifs('prior period unbilled'!$E:$E, $I4, 'prior period unbilled'!$A:$A, $H4)>0

Highlight applicable range >>
A4:T10000 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=countifs('prior period unbilled'!$E:$E, $I4, 'prior period unbilled'!$A:$A, $H4)>0

Format [Number, Font, Border, Fill] - FILL YELLOW
choose the format you would like to apply when the condition is true
OK >> OK

I4 is trying to match to column E
and
H4 is trying to match to column A
if both match it highlights the row

then it moves down a row and checks again for row 5

conditional formatting question

biglb79

Active Member

Excel Facts

etaf

Well-known Member

XL2BB - Excel Range to BBCode

Test Here

dreid1011

Well-known Member

biglb79

Active Member

XL2BB - Excel Range to BBCode

Test Here

biglb79

Active Member

etaf

Well-known Member

biglb79

Active Member

etaf

Well-known Member

cond-frmt-ETAF.xlsx

biglb79

Active Member

etaf

Well-known Member

Similar threads

Forum statistics

Share this page

conditional formatting question

Active Member

Excel Facts

Well-known Member

Well-known Member

Active Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock