LinkBack URL
About LinkBacks
In excel column B and from B6 is either a 1 or 0 entered. In Column C and from C6 I want to highlight cells at intervals of 36. e.g. C41, C77 etc based on the number of times 36 is summed from B6
In excel column B and from B6 is either a 1 or 0 entered. In Column C and from C6 I want to highlight cells at intervals of 36. e.g. C41, C77 etc based on the number of times 36 is summed from B6
Last edited by PeterThomas; 07-15-2021 at 02:01 AM. Reason: add attachment
Welcome to the forum.
There are instructions at the top of the page explaining how to attach your sample workbook.
Please update your profile to MS365. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There is no attachment, despite your post edit. Please try again.
Thanks, still finding my way around
No worries.
Unfortunately, you have not mocked up in the workbook what you want to see. Can you please add the formatting you want to achieve manually and then we can give you a possible solution.
Maybe this is what you want?
CF rule for B6:
=MOD(SUM(B$6:B6),36)=0
Hi,
In addition of Ali's solution, in C6:,Formula:
Please Login or Register to view this content.
in C5 :Formula:Please Login or Register to view this content.
Diana Tanase
If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way to say thank you !
WHY, Diana???
There is no need for visible helper columns. I don't see where the OP has asked for this ... Did I miss something?
Row 77 sum is 70 not 72.
Formula for CF.
Select C6:C81
=MOD(SUM($B$6:$B6),36)=0
select color for formatting.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
I offered that solution back in post #6!!!
Ok. I did not observed it.
Actually, your slight adaptation does meet the brief more accurately.
Sorry, Ali, my English is not perfect, I think I misunderstood looking at the data in the file.Originally Posted by AliGW
OK - thanks for clarifying!
@kvsrnivasamurthy
Hi and thank you very much. I can see your worksheet looks like the formula I was after however I was not able to see the formula array formula?
Last edited by AliGW; 07-15-2021 at 10:52 AM.
Peter - whom are you addressing???
Sorry, kvsrnivasamurthy
kvsrnivasamurthy
Hi and thank you very much kvsrnivasamurthy. I can see your worksheet looks like the formula I was after however I was not able to see the formula array formula?
What I mean is, when I enter the formula in C6 it displays FALSE, I then handle down the column and FALSE or TRUE displays. I then conditionally format the colour to highlight when TRUE which gives me the result I want however I did not want to see FALSE or TRUE displaying. I would prefer only the colour highlight on the cells at 36 intervals. Can this be achieved by adding the the formula you have done? That is, C6:C81 would display only coloured cells, the same as the sample you provided me. I am unable to see your formula in the sample returned to me. Thanks for your patience and understanding
Pl do not enter the formula in the cell. Pl see file.
Select C6:C81
Home --> Conditional formatting --> New rule
click->Use a formula to determine which cells to format
Enter the formula
=MOD(SUM($B$6:$B6),36)=0
click ->Format
Fill --> choose color --> Ok --> Ok
This completes CF.
IF Sum B6:B44 is 36 then C44 will be highlighted.
Similarly when the sum is multiple of 36 (36,72,108.....) correspondi cell in Column C will be highlighted.
kvsrinivasamurthy, Got it. Many thanks for your help.
Thank you for marking the thread as solved.
You may not be aware that you can thank those who have helped you by clicking the small star icon 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 those who helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks