I have created a spreadsheet for the Diablo 3 Auction House that tracks Gem and materials prices and then lets me know if the price is +- 15% over the previous prices, which is the auction house fee. It then tells me to buy or sell depending on that percentage. I have to manually enter the prices for all commodities, but I have also created a page that takes the entered prices and calculates the price it would cost to craft a gem. I would like conditional formatting to compare the crafted gem prices to the current market value of the same gem and take into account the 15% auction house fee to determine if it is profitable to craft a gem and then sell it.

I'm sure a lot of you have no idea what I'm talking about, so I'm attaching the code that I have so far that does the calculations to determine the cost of each gem if I were to craft it.

I know I'm long winded about this, but I want to make it as clear as possible.

In this code, for example, it takes 2 - "SA" Gems + $100 + 1 "ToJC" to craft 1 - "FSA" Gem. The value for "FSA" gem is on sheet "AM" in the last cell in column "H". I want to compare the newly craft gem price to the actual price of a "FSA" gem that I can just buy on the auction house. If the cost to craft the gem is more than 15% less than the price on the auction house, I would like it to make the text Green. If it can be done to have variations of green depending on the percentage (greater percentage of profit = dark green - barely making a profit (15.01%) light green), that would be optimal.

Sub Find_Cost()
Dim AM As Worksheet
Dim EM As Worksheet
Dim RU As Worksheet
Dim TP As Worksheet
Dim MA As Worksheet
Dim GEM As Worksheet

Dim SA As String
Dim FSA As String
Dim PSA As String
Dim RSA As String
Dim STA As String
Dim FSTA As String
Dim PSTA As String
Dim RSTA As String

Dim SE As String
Dim FSE As String
Dim PSE As String
Dim RSE As String
Dim STE As String
Dim FSTE As String
Dim PSTE As String
Dim RSTE As String

Dim SR As String
Dim FSR As String
Dim PSR As String
Dim RSR As String
Dim STR As String
Dim FSTR As String
Dim PSTR As String
Dim RSTR As String

Dim ST As String
Dim FST As String
Dim PST As String
Dim RST As String
Dim STT As String
Dim FSTT As String
Dim PSTT As String
Dim RSTT As String

Dim SuEss As String
Dim FaToo As String
Dim ShEss As String
Dim LiEye As String
Dim WiEss As String
Dim EnHoo As String
Dim ExEss As String
Dim IrTea As String
Dim FiBri As String
Dim PoBS As String
Dim PoJC As String
Dim ToBS As String
Dim ToJC As String
Dim ToS As String

Set AM = Sheets("Amethyst")
Set EM = Sheets("Emerald")
Set RU = Sheets("Ruby")
Set TP = Sheets("Topaz")
Set MA = Sheets("Mats")
Set GEM = Sheets("Gems")


SA = AM.Range("F500").End(xlUp)
FSA = AM.Range("H500").End(xlUp)
PSA = AM.Range("J500").End(xlUp)
RSA = AM.Range("L500").End(xlUp)
STA = AM.Range("N500").End(xlUp)
FSTA = AM.Range("P500").End(xlUp)
PSTA = AM.Range("R500").End(xlUp)
RSTA = AM.Range("T500").End(xlUp)

SE = EM.Range("F500").End(xlUp)
FSE = EM.Range("H500").End(xlUp)
PSE = EM.Range("J500").End(xlUp)
RSE = EM.Range("L500").End(xlUp)
STE = EM.Range("N500").End(xlUp)
FSTE = EM.Range("P500").End(xlUp)
PSTE = EM.Range("R500").End(xlUp)
RSTE = EM.Range("T500").End(xlUp)

SR = RU.Range("F500").End(xlUp)
FSR = RU.Range("H500").End(xlUp)
PSR = RU.Range("J500").End(xlUp)
RSR = RU.Range("L500").End(xlUp)
STR = RU.Range("N500").End(xlUp)
FSTR = RU.Range("P500").End(xlUp)
PSTR = RU.Range("R500").End(xlUp)
RSTR = RU.Range("T500").End(xlUp)

ST = TP.Range("F500").End(xlUp)
FST = TP.Range("H500").End(xlUp)
PST = TP.Range("J500").End(xlUp)
RST = TP.Range("L500").End(xlUp)
STT = TP.Range("N500").End(xlUp)
FSTT = TP.Range("P500").End(xlUp)
PSTT = TP.Range("R500").End(xlUp)
RSTT = TP.Range("T500").End(xlUp)

ShEss = MA.Range("F500").End(xlUp)
LiEye = MA.Range("H500").End(xlUp)
WiEss = MA.Range("J500").End(xlUp)
EnHoo = MA.Range("L500").End(xlUp)
ExEss = MA.Range("N500").End(xlUp)
IrTea = MA.Range("P500").End(xlUp)
FiBri = MA.Range("R500").End(xlUp)
PoBS = MA.Range("T500").End(xlUp)
PoJC = MA.Range("V500").End(xlUp)
ToBS = MA.Range("X500").End(xlUp)
ToJC = MA.Range("Z500").End(xlUp)
ToS = MA.Range("AB500").End(xlUp)


GEM.Range("B2") = (SA * 2 + 100 + ToJC)
GEM.Range("B3") = (SE * 2 + 100 + ToJC)
GEM.Range("B4") = (SR * 2 + 100 + ToJC)
GEM.Range("B5") = (ST * 2 + 100 + ToJC)

GEM.Range("C2") = (FSA * 3 + 30000 + (ToS * 3))
GEM.Range("C3") = (FSE * 3 + 30000 + (ToS * 3))
GEM.Range("C4") = (FSR * 3 + 30000 + (ToS * 3))
GEM.Range("C5") = (FST * 3 + 30000 + (ToS * 3))

GEM.Range("D2") = (PSA * 3 + 50000 + (ToS * 6))
GEM.Range("D3") = (PSE * 3 + 50000 + (ToS * 6))
GEM.Range("D4") = (PSR * 3 + 50000 + (ToS * 6))
GEM.Range("D5") = (PST * 3 + 50000 + (ToS * 6))

GEM.Range("E2") = (RSA * 3 + 80000 + (ToS * 9))
GEM.Range("E3") = (RSE * 3 + 80000 + (ToS * 9))
GEM.Range("E4") = (RSR * 3 + 80000 + (ToS * 9))
GEM.Range("E5") = (RST * 3 + 80000 + (ToS * 9))

GEM.Range("F2") = (STA * 3 + 100000 + (ToS * 12))
GEM.Range("F3") = (STE * 3 + 100000 + (ToS * 12))
GEM.Range("F4") = (STR * 3 + 100000 + (ToS * 12))
GEM.Range("F5") = (STT * 3 + 100000 + (ToS * 12))

GEM.Range("G2") = (FSTA * 3 + 200000 + (ToS * 15))
GEM.Range("G3") = (FSTE * 3 + 200000 + (ToS * 15))
GEM.Range("G4") = (FSTR * 3 + 200000 + (ToS * 15))
GEM.Range("G5") = (FSTT * 3 + 200000 + (ToS * 15))

GEM.Range("H2") = (PSTA * 3 + 400000 + (ToS * 20))
GEM.Range("H3") = (PSTE * 3 + 400000 + (ToS * 20))
GEM.Range("H4") = (PSTR * 3 + 400000 + (ToS * 20))
GEM.Range("H5") = (PSTT * 3 + 400000 + (ToS * 20))


End Sub