Conditional Formatting in Excel to Create Fill Background

I have an Excel spreadsheet with Conditional Formatting.  (Attached). When any value appears in H20 and J20, Cells B20 through X20 are to have a fill background. It is currently formatted to do that but I do not believe it is correctly formatted (If you modify anything, suddenly the fill background drops out on P20 through X20! And, I want to do the same thing on each line, lines 4 through 28 and lines 34 through 58 and be able to create sheet after sheet below the current sheet with the conditional formatting following.
JTL-Log-17126-17150-EE4.xlsm
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill GoldenExecutive Managing MemberAuthor Commented:
Please note: When I do a Copy and Paste Special specifying only Conditional Formatting, it copies the cell values as well. When I only copy specifying Formatting, it copies the fill, but not the conditional formatting commands. This is why I do not think the Conditional Formatting is correctly set to begin with.
0
Rob HensonFinance AnalystCommented:
You only have the CF set on row 20.

Change the formula to:
=$H4>0

And then change the "applies to" range to:
=$B$4:$X$28

For the second set, formula should be:
=$H34>0
And applies to:
=$B$34:$X$58

For subsequent sets, so long as the formula refers to the first row of the "applies to" range then it should work.
1
Rob HensonFinance AnalystCommented:
BTW there is an actual tick mark in WingDings font rather than using the square root symbol.

Format the cells as Wingdings and then press Alt and type 0252 on the numberpad, not the numbers across top of the keyboard.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Thymos68Director of OperationsCommented:
Just a note, I find it much cleaner to copy and paste formulas when I'm working with conditional formatting.  Otherwise, you'll start chopping up the conditional formatting ranges everytime you copy and paste, which stresses out Excel.
0
Bill GoldenExecutive Managing MemberAuthor Commented:
Thanks Rob and Thymos68,

One thing, I need the condition based on values being BOTH in H4 and J4. How would I phrase that?
0
Thymos68Director of OperationsCommented:
That would be written like this:  =AND($H4>0,$J4>0)
  (the result is TRUE only when H and J are greater than zero. )

As an aside, you have an odd way of formatting the dates.  (forcing you to enter January 1st as "1012018", instead of simply 1/1, or 1/1/18 or 1/1/2018)
I would suggest you enter the dates as actual dates and let Excel handle them as dates.  That way they can be sortable, or easily reformatted.  You can still format them to display the way you currently have them by changing the custom number formatting to MM-DD-YY.  (the only drawback is that you would have to re-enter all of your current dates, so Excel can recognize them for what they are.)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill GoldenExecutive Managing MemberAuthor Commented:
That fixed it!  Thanks.  As for the date thing, we several software programs using the MMDDYY without dashes or slashes approach. With the volume of entry in those programs, the average user would enter a - or / more than 10,000 times per year. Therefore, we made Excel to work the same way for consistency. Thanks again.
0
Rob HensonFinance AnalystCommented:
How about sharing and giving some assistance points?
0
Thymos68Director of OperationsCommented:
Certainly a group effort
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.