I have a spreadsheet in which I enter daily closing stock prices.

I want to be able to automatically have the cell fill with a color that indicates whether the current day's closing price is one of the following:

1. Higher than the previous day's closing price
2. Lower than the previous day's closing price
3. The same as the previous day's closing price

I have attached part of the spreadsheet I use (and manually fill in the current day cell color based on closing price change from the previous day's closing price). There are days with no data - SAT, SUN, holiday that need to be accounted for so that the correct previous day is referenced.

I appreciate the help.

Thanks.

Attached Files

092318_Excel_Forum_Sample_Spreadsheet.xlsx (10.2 KB, 11 views)

How about this? Notice on a holiday and/or on a weekend, you need to transfer the values from the day before in order to fulfill the desired conditional format for the next trading day. The font and fill are both grey.

You posted this thread in Excel Programming, but I don't see any reason why you need any VBA as you can achieve this thru CF.

Attached Files

092318_Excel_Forum_Sample_Spreadsheet (1).xlsx (11.5 KB, 8 views)

I see what you mean by having the FRI value repeated on SAT and SUN so that no "special" handling would be required. I like that.

I am new to the forum so I apologize if I posted it in the incorrect place.

I do not know how to accomplish my desired result using conditional formatting in this regard. That was really the main thrust of what I was asking help on.

Thanks very much for your reply. That got me part of the way there.

A possible solution is in attached workbook.

Conditional formatting is defined for column B. I think you can apply the same logic for other columns too. The colors for conditional formatting are selected as near of yours as was possible from default ones in my color palette. Holidays list is a fake one - I added the one you had in your example.

Edit: Weekday numbers in column E are calculated as ISO weekday numbers (European ones). With US weekday numbers the formulas in other columns will be much more complex, and this will affect workbooks performance when data amount increases.

Attached Files

092318_Excel_Forum_Sample_Spreadsheet.xlsx (17.7 KB, 18 views)

Thanks for your reply.

I somewhat understand what you have done. What I don't get is the mechanics of how the cells in A5:A16 get the appropriate colors assigned. Can you please let me know how this step is done?

Thank you.

I imagine you are speaking about the last post which seems overkill to me. Why create a column (column E) of formulas to do the conditional formatting when you can apply the condition directly to the cells.

Maybe looking at some tutorials will help...

https://exceljet.net/conditional-for...-with-formulas
https://www.contextures.com/xlCondFormat03.html
https://www.ablebits.com/office-addi...matting-dates/

These are just a few tutorials pasted around the internet. We could retype these steps, but since they are already typed out and not exactly sure what you are understanding, there should help.

I defined tHolidays as a Table (to make it dynamic). Conditional formatting doesn't recognize Tables, and it doesn't work with named ranges either.

The number of calculated columns may be reduced, but I kept every step to make for OP easier to understand the logic. And the number of calculated columns is really moot, OP can hide them, and having several columns with simple formulas may be advantageous compared with one column with super-complex formula.

Instead of OP, I'd define the table on data sheet as Table too. Currently I left it as it was originally, but this means whenever a new rows of data are added (or rows are removed), all range references must be edited in all formulas - which means a lot of unneeded work. (And all conditional formatting rules must be edited too, when OP wants to do all calculations there. And those calculations will get quite complex too with references to several ranges on different sheets for every condition.) With Table defined on data entry sheet and all formulas replaced with Table formulas, the whole workbook will be dynamic. But this means, that there mus be a column in data entry Table, which determines conditional formatting condition for every row.

Search for Conditional Formatting in help!

Activate a cell in column B with prices, and select from Menu Home>Conditional Formatting>Manage Rules. 5 rules (a rule for every color) are defined there.

When you want to apply conditional formatting to columns C and D too, you need additional 2 calculated columns with previous workday values for those columns, and additional 5 conditional formatting rules for every of those columns.

You might want to check the part about CF not working with named ranges.

I defined a block of 3 numbers and assigned the named range MyNumbers to it. In those cells I typed in 1, 1, and 1.

As an example, I used in CF >> =SUM(MyNumbers)=3

Works okay for me.

Thank you all. The videos online suggested were a great help. I finally got it!

This thread can now be marked closed.

Thanks again.