Hi. I would like a cell to be either "green2 or "red" depending on 2 other cell that are dates. Example if there are dates inserted on A1 and B1 than C1 is green but if there is only a date inserted on A1 (B1 is blank) than C1 is red...
Can it be done?
Thanks
Use conditional formatting on C1 and make 2 rules. RED rule using a formulaGREEN rule using formula
Please Login or Register to view this content.then copy it down column CPlease Login or Register to view this content.
Thanks. That, almost works. Had to put the red rule also >0 ( =$B1>0 insted =$B1=0), and works like a charm. Is there an easy way of making a column work all "like that" meaning Cn depending of An and Bn? Or have to make "new" rule to every cell? I just have to make like...100 rules
If you remove the $ you should be able to copy across the sheet. The $ is a absolute reference that holds it in column BOriginally Posted by jlavado
You're a miracle worker
Thanks. Works just fine
Lot of work saved
Hi all again
Another question(s):
- I need C1 to be blank if either A1 and C1 are blank, C1 Red if A1 is with a date but if both A1 and C1 are with a date than C1 is Green
- Can I make the sheet (Those chosen from bottom) a color filled depending on, lets say, the above C1? Or if there is any color filled cell?
Last edited by jlavado; 12-11-2020 at 07:16 AM.
Hello jlavado and Welcome to Excel Forum.
Your first point seems to have a contradiction. If only A1 has a date then that seems to mean that C1 is empty so that would satisfy the condition for both no fill or red fill.
As far as green fill, because Excel uses the number of days since Dec 31, 1899 to assign dates, try: =AND(ISNUMBER(A1),ISNUMBER(C1))
I don't understand the second request. Perhaps utilizing the instructions in the banner at the top of the page and supplying an .xlsx file as a manually highlighted example which we could attempt to automate work may help.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hi.
As for the 1st issue, I was able to solve it, by using some different logic than I was doing, and some priorities.
As for the 2nd issue, the idea is if the status is red (see Ex1 Red.jpg in attachments) than the below page descrition is red,
if the status is green (see Ex2 Green.jpg in attachments) than the below page descrition is green.
Trying my best to describe this, as English is not mother language, and Excel is not my strong...
I believe that conditionally coloring the worksheet tab requires VBA, of which I know very little.
I will ask some of the VBA forum contributors to take a look at this thread.
I still suggest uploading an .xlsx file rather than screen shots as screen shots can't be used for testing code.
The VBA contributors will probably need to know the logic that causes some cells in the worksheet to be colored red and others green.
I hope that you have a blessed day.
The VBA contributors will probably need to know the logic that causes some cells in the worksheet to be colored red and others green.
(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)
- Click Advanced next to "Post Quick Reply" button at the bottom right of the editor box.
- Scroll down until you see "Manage Attachments",
- Click the "Choose" button at the upper left (upload from your computer).
- Select your file, click "open", click "upload"
- Once the upload is completed the file name will appear below the input boxes in this window.
- Close the Attachment Manager window.
- Click "Submit Reply"
Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!
Ben Van Johnson
Hi
If really needed to upload the .xlsx file, I will, but because this is some work being done to a company, I rather prefer trying without uploading them...but if there's no other way...
Hi there,
I haven't tried to get to grips with the logic of your conditional formatting process, but see if the attached workbook gets you moving in the right direction.
If any cell in the range F1:J1 has a red background due to conditional formatting, the sheet tab will be coloured red.
If no cell in the range F1:J1 has a red background due to conditional formatting, the sheet tab will be coloured green.
The following code is used in the VBA CodeModule of the worksheet under test:
Please Login or Register to view this content.
Hope this helps - please let me know how you get on.
Regards,
Greg M
Last edited by Greg M; 12-21-2020 at 03:00 PM. Reason: Inappropriate line highlighted in red - see next post
Sorry! Just noticed that it is more appropriate to use the following line:
instead of:Please Login or Register to view this content.
Please Login or Register to view this content.
Either line will "work", but the original version calls the routine many times more than is actually required.
Regards,
Greg M
Hi.
First, Merry Christmas to you all.
This works fine, for one line... I used it P8 ("Status") whitch already covers it up.But the need is for all the Pn (P8, P9, P10, Pn). How to do it?
Hope I got the attachment right...
Thanks
Hi again,
Thanks for your feedback.
Take a look at the attached version of your workbook and see if it does what you need. It uses the following code in the "ThisWorkbook" VBA CodeModule:
The highlighted values may be altered to suit your requirements.Please Login or Register to view this content.
Hope this helps - as before, please let me know how you get on.
Regards,
Greg M
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks