How to use conditional formatting only on filled cells?

[rizwanulhasan]

Hi all,

Hope all are well.

In attached sheet, i am using CF in column G such that if cells under column G doesn't start with below options, then highlight in red.

DM_ATTP_GSK
DM_ATTP_CMO
DM_ATTP_CUST
DM_ATTP_VX


I used below formula but the issue is that even blank cells are highlighted in red.

Please Login or Register  to view this content.

Can someone please help

[russkris]

try this instead

Please Login or Register  to view this content.

[rizwanulhasan]

@russkris

Thanks for your response.

Tried but now it highlights few cells and not others. Attached file.

[rizwanulhasan]

Just an update - Cells should be considered from G4

[russkris]

Please Login or Register  to view this content.

DM_ATTP_CUST wasnt included. sorry

[FDibbins]

To start with, dont use full column references with CF, it can slow your file down. Use 2-3 times the range that you think you might need...I used =$G$4:$G$500. (this will stop the headings from being tested)

Try this instead for the 1st C ryule...
=AND(ISTEXT(G4),OR(LEFT(G4,12)<>"DM_ATTP_GSK",LEFT(G4,12)<>"DM_ATTP_CMO",LEFT(G4,12)<>"DM_ATTP_CUST",LEFT(G4,12)<>"DM_ATTP_VX"))

(russkriss in your suggestion, you reference G1 instead of G4 in 2 of your conditions. Also, CF only needs a TRUE or FALSE answer, you can do away with the IF statement)

[rizwanulhasan]

@FDibbins

Many thanks for your response and suggestion on not using full column references with CF.

Its working now.

[protonLeah]

Workbook has code running in a password protected vba project

[FDibbins]

Happy to help and thanks for the feedback

[rizwanulhasan]

Workbook has code running in a password protected vba project

Since formula was required for CF, i didnt unprotect the VBA. My apologies.

[rizwanulhasan]

Sorry but 1 help

Please Login or Register  to view this content.

Can 1 more condition be added in above formula such that the cells should be highlighted if date in column E is on or after 01-03-2024

To elaborate, if date in column E is on or after 01-03-2024 and column G doesn't start with below options, then highlight cell in red.

DM_ATTP_GSK
DM_ATTP_CMO
DM_ATTP_CUST
DM_ATTP_VX

Thanks in advance.

[DJunqueira]

Do you mean 01-03-2024 as a hard date or a TODAY() date?

Tip:
Don't use the conditional format for the entire column. As you have a named table use the formatting there and as you add new data the CF will follow.
The same for the rows border, you don't need to format it in advance, the table will take care of it as you add new data.

[rizwanulhasan]

01-03-2024 will be constant

May i know how to use CF and borders for a named table?

[DJunqueira]

You just use the CF inside of the body area of the named table (not the header) and as it expand it will carry it.
I did it for you, BUT you have more then 20 CF rules, probably many of them are contradicting each other, so before implementing any new rule better decide which one will continue there or not!

[rizwanulhasan]

Many thanks for the suggestion on CF. I'll follow it.

Also thanks for modifying the below formula but it doesn't seem to work. Although date in column E is before 1st March, the cell is highlighted in red

Please Login or Register  to view this content.

[DJunqueira]

This might be because of your other CF that are contradictory. Did you erase all other to test?

[rizwanulhasan]

Yes i tried. It seems the issue is with format. Its turning red on and after 03 Jan 2024 instead of 1st March 2024.

[AliGW]

Here's why:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	E	F	G
1	45294	=	03/01/2024
2	45352	=	01/03/2024

Sheet: Sheet1

=AND(ISTEXT(G7),OR(LEFT(G7,12)<>"DM_ATTP_GSK",LEFT(G7,12)<>"DM_ATTP_CMO",LEFT(G7,12)<>"DM_ATTP_CUST",LEFT(G7,12)<>"DM_ATTP_VX"),E7>=45294)

Try:

=AND(ISTEXT(G7),OR(LEFT(G7,12)<>"DM_ATTP_GSK",LEFT(G7,12)<>"DM_ATTP_CMO",LEFT(G7,12)<>"DM_ATTP_CUST",LEFT(G7,12)<>"DM_ATTP_VX"),E7>=45352)

[DJunqueira]

I inherited a wrong formula...

Please Login or Register  to view this content.

LEFT(G7,11)<>"DM_ATTP_GSK",
LEFT(G7,11)<>"DM_ATTP_CMO",
LEFT(G7,12)<>"DM_ATTP_CUST",
LEFT(G7,10)<>"DM_ATTP_VX"

[rizwanulhasan]

Try:

=AND(ISTEXT(G7),OR(LEFT(G7,12)<>"DM_ATTP_GSK",LEFT(G7,12)<>"DM_ATTP_CMO",LEFT(G7,12)<>"DM_ATTP_CUST",LEFT(G7,12)<>"DM_ATTP_VX"),E7>=45352)

Thanks AliGW, but still same issue.

[DJunqueira]

Eventually the formula bellow could be a good alternative since you would use LEFT() less times.

Please Login or Register  to view this content.

[rizwanulhasan]

I inherited a wrong formula...

Please Login or Register  to view this content.

LEFT(G7,11)<>"DM_ATTP_GSK",
LEFT(G7,11)<>"DM_ATTP_CMO",
LEFT(G7,12)<>"DM_ATTP_CUST",
LEFT(G7,10)<>"DM_ATTP_VX"

Thanks for the correction. Date still remains the issue.

[rizwanulhasan]

Thanks AliGW, but still same issue.

My apologies. It works

[AliGW]

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, remember that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[rizwanulhasan]

Another issue now.

Although cell G4 starts with DM_ATTP_GSK, yet its highlighted in red.

Just to clarify that below is th requirement

If date in column E is on or after 01-03-2024 (1st March 2024) and column G doesn't start with below options, then highlight cell in red.

DM_ATTP_GSK
DM_ATTP_CMO
DM_ATTP_CUST
DM_ATTP_VX

[DJunqueira]

The date sample you gave us didn't fall in the range of that date.

As a final formula.

Please Login or Register  to view this content.

[rizwanulhasan]

The date sample you gave us didn't fall in the range of that date.

As a final formula.

Please Login or Register  to view this content.

Same issue. Although cell G4 starts with DM_ATTP_GSK, yet its highlighted in red.

[DJunqueira]

New test.

Please Login or Register  to view this content.

[rizwanulhasan]

New test.

Please Login or Register  to view this content.

Perfect. Thank you so much

[AliGW]

Thanks for the rep.

[DJunqueira]

Tks for the feedback, glad to help.