Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

conditional formating

0

I'm trying to get conditional formating to highlight the next cell below it after the information has been entered

ie: c3= 07/12/19 nighlights c4 (Yellow is for input  zones but I only want the color to show as they're needed)

All other colors are for automatic calculations.

I would like the blue cells to only appear when the word "Interest" is entered in column C

The green cells will be hiden once everything is sorted as they are just for the interest calulations

EDIT

I uploaded final workbook. Sheet one shows finished sheet (no passwword to unprotect sheet), sheet 2 shows unhiden columns etc

Thankyou to WillieD and John_Ru

excel
MS Office Version: 2019
posted by: wildecoyote1966 (rep: 30) on Sat Sep 18, 2021 at 10:52 am
Answer
Discuss

Discussion

Hello Wilde,
I didn't see this question until after I updated my original post.

I'm a bit confused about what you are looking to achieve. You have changed your colour choices from your original post. You can change the colours to your personal preference by editing the CF rules.

Only Col's B, C and G require an entry; Cols E, F, and G are calculated by youur formulas.

If "Interest" is entered in Col C the formula in Col E (Debit) automatically calculates the amount. If "Repayment" is entered in Col C then the cell in Col F (Credit) is turned blue to indicate an entry is required.

WillieD
WillieD24 (rep: 547) Sep 19, '21 at 12:19 pm
I actually left eveything pretty much as you had it (sheet 2) and made a couple of minor adjustments to other formula and then I was asking if you could fix the red column. 
Sorry I'm not good at explaining stuff in writing but I'll try. 

So I would like the total column G to display the total with red as it shows up if that makes sense.  I'm happy with everything else and can change colours later as I do know how to do that. 
I appreciate your help and I'm grateful as it should help my husband use it. I try to involve him in our financial stuff.
wildecoyote1966 (rep: 30) Sep 19, '21 at 12:51 pm
Hi Wilde,

I have updated my original post a second time, hopefully the updated file attached sloves your questions and has the look you were working towards.

Have a great weekend
WillieD24 (rep: 547) Sep 19, '21 at 3:08 pm
Add to Discussion

Answers

0
Selected Answer

Hi Wilde,

I made a copy of your sheet1 and made my chages there - your sheet1 was not altered.

Here is what I did (see attached file)

Original Conditional Formatting rules removed and new ones added (Col's B,C, E, F & G; rows 18-167) 

Col's B & C: if the cell is empty then no fill; if there is an entry the fill is light 'gold', if the cell is empty and the cell above is not then the fill is blue.

Col E: if the entry in Col C is "Interest" the fill in Col E is yellow.

Col F: if the entry in Col C is "Repayment" and there is no entry in Col F the fill is blue; if there is an entry the fill is light 'gold'

Hope this helps,

WillieD24

UpDate (2nd) - Spet. 19:

Revised file is attached

I couldn't quickly find a way to highlight only the last value in Col G but now only the cell after the last value is filled red. I also added Data Validation to Col C to eliminate typing and possible spelling errors. I also tweaked your formula in Col G to check for <>"" instead of =""

All formulas have been checked for correct cell references and have been copied down to row 167 (that was the end row you used in your CF rules).

Added CF rules to Col's H, I, & J so they are only green when then hold a value (Col J is dependant on Col I)

Cheers,   WillieD

posted by: WillieD24 (rep: 547) on Sat Sep 18, 2021 at 7:50 pm
Discuss

Discussion

Hi WillieD24

yes that's great. can you fix it so the total column (G) only displays the fill to the last shown number and not all the way down please?
wildecoyote1966 (rep: 30) Sep 18, '21 at 9:32 pm
I have updated my original post and attached the updated file. I have entered values into the cells which were missing them. If a value is missing there will be errors shown.
WillieD24 (rep: 547) Sep 19, '21 at 11:58 am
Oh I just noticed the drop down list.  I hadn't even given that a thought.  Thank you. I haven't used that in years
wildecoyote1966 (rep: 30) Sep 19, '21 at 12:56 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login