Hi folks, I have a need to apply some conditional formatting to 2 cells inside a spreadsheet that contains significant levels that I wish to watch. The standard conditional formatting allows one to apply 3 conditions (max) to any cell. However, I want the cell's background color to change depending on a lookup (indexing) formula that fetches a result from a range. I was wondering if someone who has experience with Excel VB can provide the short script needed to create this ability. I can then modify the script to make it fit my needs. I have attached a small spreadsheet that one can download and test their script on before uploading it again. I have around 30 different cells in the indexed range that must be referenced. Please have a look if you have VB formatting experience and let me know how I can apply this. For those of you who will respond to refer me to some book, please don't bother. I can figure it out given the time. My intention is to ask for help from someone who already knows the answer to this simple problem. Thanks. FT71
This will do the trick : Sub FormatCells() Dim AskIndicatorAddress As String Dim BidIndicatorAddress As String Dim AskIndicator As Variant Dim BidIndicator As Variant Dim FoundMatch As Boolean Dim MatchRow As Variant Dim MatchInteriorFormat As Variant Application.EnableEvents = False '********Get rid of existing conditional formats Range("B4:B5").FormatConditions.Delete Range("B4:B5").Interior.ColorIndex = xlNone '************************************* AskIndicatorAddress = "b4" BidIndicatorAddress = "b5" AskIndicator = Range(AskIndicatorAddress).Value BidIndicator = Range(BidIndicatorAddress).Value '**************Find the Ask_Indicator MatchRow = 8 FoundMatch = False Do While FoundMatch = False And Cells(MatchRow, 1).Value <> "" If Cells(MatchRow, 2).Value + 0 = AskIndicator + 0 Then FoundMatch = True MatchInteriorFormat = Cells(MatchRow, 1).Interior.ColorIndex Range(AskIndicatorAddress).Interior.ColorIndex = MatchInteriorFormat Else MatchRow = MatchRow + 1 End If Loop If Cells(MatchRow, 1).Value = "" Then MsgBox ("Could not find Ask_Indicator") '**************Find the Bid_Indicator MatchRow = 8 FoundMatch = False Do While FoundMatch = False And Cells(MatchRow, 1).Value <> "" If Cells(MatchRow, 2).Value + 0 = BidIndicator + 0 Then FoundMatch = True MatchInteriorFormat = Cells(MatchRow, 1).Interior.ColorIndex Range(BidIndicatorAddress).Interior.ColorIndex = MatchInteriorFormat Else MatchRow = MatchRow + 1 End If Loop If Cells(MatchRow, 1).Value = "" Then MsgBox ("Could not find Bid_Indicator") Application.EnableEvents = True End Sub I'd guess you'd want this to run each time any of the relevant cells changes value....if so, and you're unsure of how to do this, let me know.
Thanks so much for your help. Both the current bid/ask and the bid/ask indicator are continuously changing cells. However, the cells they reference to determine the color are static. How does effect this code?
Right then : As you've no doubt already noticed, this code works fine but needs to be run manually each time you want to have the correct colour displayed. Which is no good, realistically, because you want to be sat on your balcony getting drunk in the sunshine, not clattering away at your keyboard in a darkened room. What I would assume you want to happen is for that code to run automatically each time the value of either the Ask_Indicator or Bid_Indicator cells change. There's an easy way to do this : 1) Copy the code as-is into the worksheet itself - ie. after copying the text, right click on the "Sheet1" tab in your Excel workbook, select "View Code", then paste. 2) Replace the "Sub FormatCells()" at the top of the code with "Private Sub Worksheet_calculate()". OK, so now the code runs automatically each time the value in B4 or B5 (or any other cell, for that matter) changes - but ONLY if that change is as a result of Excel re-calculating the cell's value. In other words, I'm assuming that you have formulas in the relevant cells which link to an API, or whatever, and which are continuously updated - each time an update occurs anywhere, the code runs. If you are typing values into cells manually, god forbid, and you want this to trigger the macro then instead of "Private Sub Worksheet_calculate()" as above you need "Private Sub Worksheet_Change(ByVal Target As Excel.Range)". Give it a go, and see if it works for you. If not, or if any of my assumptions are wrong, let me know.
I am aware that I need to change it to on-calculate. I knew that something was wrong because it would only work when I first start it. Thanks for pointing this out. I will make the adjustments and let you know if it works. Thanks for your PM as well.