Conditional Formatting Enquiry

%3CLINGO-SUB%20id%3D%22lingo-sub-1615551%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20Enquiry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1615551%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%3EHi%2C%20when%20I%20do%20the%20conditional%20formatting%2C%20some%20of%20the%20cells'%20category%20changed%2C%20for%20example%2C%20the%20column%20should%20be%20the%20%22Date%22%20format%20but%20all%20the%20cells%20changed%20to%20general%20category%2C%20I%20have%20to%20format%20the%20cells%20every%20time%20after%20the%20conditional%20formatting%2C%20is%20there%20any%20method%20that%20could%20avoid%20this%20%3F%20%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1615551%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1615580%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Enquiry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1615580%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F772913%22%20target%3D%22_blank%22%3E%40Kerokero%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20is%20strange.%20Is%20any%20number%20format%20applied%20within%20the%20rule%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1615606%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Enquiry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1615606%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20use%20a%20formula%20to%20determine%20which%20cells%20to%20format%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DA2%26lt%3B%26gt%3B%E2%80%990826%E2%80%99!A2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20use%20this%20to%20compare%20two%20worksheets%20(let%20say%20%3CSPAN%3E%C3%A0%3C%2FSPAN%3E%20yesterday%20-%200826%20and%20today%20-%200827)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20below%2C%20F2%20to%20F7%2C%20the%20cells%20not%20indicate%20as%20Date%20format%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20advise.%20Thank%20you%20so%20much%20!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Kerokero_0-1598517738214.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214943i5F62BEBD3FD260C0%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Kerokero_0-1598517738214.png%22%20alt%3D%22Kerokero_0-1598517738214.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20because%20A2%20cell%20on%20the%20table%20is%20general%20category%20%3F%20So%20that%20all%20the%20cells%20that%20I%20use%20format%20painter%20will%20become%20general%20category%20as%20well%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20your%20reply.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1616008%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Enquiry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1616008%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F772913%22%20target%3D%22_blank%22%3E%40Kerokero%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20you%20don't%20need%20format%20painter%2C%20you%20may%20apply%20the%20rule%20to%20entire%20range%2C%20e.g.%20F2%3AF100.%20Keep%20one%20colour%20-%20one%20rule%20approach.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormat%20painter%20applies%20entire%20formatting%2C%20not%20only%20conditional%20one.%20Thus%20couple%20of%20more%20questions%3C%2FP%3E%0A%3CP%3E-%20if%20you%20add%20very%20first%20time%20conditional%20formatting%20rule%20to%20F2%20(no%20one%20rule%20was%20before%20that)%20is%20it%20converts%20F2%20value%20from%20Date%20to%20Number%20format%3F%3C%2FP%3E%0A%3CP%3E-%20if%20so%2C%20what%20is%20here%20in%20Format%20section%20of%20the%20rule%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20685px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F215009i51FF842A4EA66FDF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1616153%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Enquiry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1616153%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20need%20to%20compare%20all%20the%20columns%20between%202%20worksheets%2C%20in%20order%20to%20sort%20out%20what's%20difference%20between%202%20worksheets%2C%20I%20have%20to%20format%20and%20fill%20with%20colour.%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EThanks%20!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1616371%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Enquiry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1616371%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20format%20categories%20are%20%22General%22%20at%20first%2C%20after%20formatting%2C%20some%20columns%20in%20%22Date%22%20showing%20numbers%20instead%20of%20Date%20format%2C%20therefore%20I%20need%20to%20change%20the%20category%20from%20%22General%22%20to%20%22Date%22%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1620830%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Enquiry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1620830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F772913%22%20target%3D%22_blank%22%3E%40Kerokero%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20question%20is%20not%20how%20the%20cell%20is%20formatted%2C%20but%20what%20is%20number%20format%20within%20the%20rule.%20Perhaps%20you%20may%20submit%20sample%20file%20removing%20sensitive%20informtaion%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, when I do the conditional formatting, some of the cells' category changed, for example, the column should be the "Date" format but all the cells changed to general category, I have to format the cells every time after the conditional formatting, is there any method that could avoid this ?

6 Replies

@Kerokero 

That is strange. Is any number format applied within the rule?

@Sergei Baklan 

 

Hi Sergei,

 

I use a formula to determine which cells to format

 

=A2<>’0826’!A2

 

I use this to compare two worksheets (let say à yesterday - 0826 and today - 0827)

 

As below, F2 to F7, the cells not indicate as Date format

 

Please advise. Thank you so much !

 

 

Kerokero_0-1598517738214.png

 

Is it because A2 cell on the table is general category ? So that all the cells that I use format painter will become general category as well ?

 

Thanks in advance for your reply.

 

@Kerokero 

In general you don't need format painter, you may apply the rule to entire range, e.g. F2:F100. Keep one colour - one rule approach.

 

Format painter applies entire formatting, not only conditional one. Thus couple of more questions

- if you add very first time conditional formatting rule to F2 (no one rule was before that) is it converts F2 value from Date to Number format?

- if so, what is here in Format section of the rule?

image.png

@Sergei Baklan 

 

But I need to compare all the columns between 2 worksheets, in order to sort out what's difference between 2 worksheets, I have to format and fill with colour.

 
 

Thanks !

@Sergei Baklan 

 

All format categories are "General" at first, after formatting, some columns in "Date" showing numbers instead of Date format, therefore I need to change the category from "General" to "Date" format.

 

@Kerokero 

The question is not how the cell is formatted, but what is number format within the rule. Perhaps you may submit sample file removing sensitive informtaion?