I though some one must have already done this, but I cannot find the ready to use code. So I had to write one myself:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Column
Dim Row
Dim Red
Dim Green
Dim heat
For Each c In Target
Column = c.Column
Row = c.Row
Rem # Define the range of the cells to change color, keep this to minimal for better performance
If Column > 1 And Column <= 12 And Row > 1 And Row < 40 Then
If c.Value = "" Then
c.Interior.ColorIndex = -4142
Else
Rem # Calculate the heat. You can times c.Value by a factor to control the range
heat = c.Value - 255
If (heat > 255) Then heat = 255
If (heat < -255) Then heat = -255
Rem # Set back color of the cell
If (heat > 0) Then
Green = 256 - heat
Red = 255
Else
Green = 255
Red = 255 + heat
End If
c.Interior.Color = RGB(Red, Green, 0)
Rem # Set fore color of the cell
If (heat > 100) Then
c.Font.Color = vbWhite
Else
c.Font.Color = vbBlack
End If
End If
End If
Next
End Sub
7 comments:
So this works for every cell that has a number in it? I am new to VBA, so excuse my question. I assume you only need this code with really big sheets? Since setting conditional formatting manually seems pretty fast and easy, at least according to this guide: http://www.excel-aid.com/excel-color-scales-highlighting-cells-with-color-scales.html . But dont give too much on my opinion, i am pretty unexperienced and just starting to get deeper into excel and stuff :)
@Markus thanks for your comment. Conditional formatting works very well for background color but I didn't find a way to change text color. Black text on dark background looks pretty bad.
I like how this article is composed. Your focuses are sound, unique, new, and fascinating. This data has been made so clear it is highly unlikely to misjudge it. Much thanks to you.
Denial management software
Denials management software
Hospital denial management software
Self Pay Medicaid Insurance Discovery
Uninsured Medicaid Insurance Discovery
Medical billing Denial Management Software
Self Pay to Medicaid
Charity Care Software
Patient Payment Estimator
Underpayment Analyzer
Claim Status
Thank you for your attention to detail and exceptional writing style. Your article demonstrates your demonstrable skill. I enjoy your fascinating points of view and respect your remarkable ideas. This is high-quality work. Sometime you can play among us or run 3 with me, if you want
Hello
My name is Elisabeth and I would like to contribute some articles for your website
Please let me know if you are accepting Guest or Sponsored Posts with dofollow link inside
I would like to know all the guidelines to write a perfect article for you
Best Regards
Elisabeth Muller
elismullermarketing@gmail.com
Hii
Thank you for the informative article. I appreciate the composition of this article. Your points are well-founded, distinct, innovative, and intriguing. Here is sharing some Big Data Hadoop Course journey information may be its helpful to you.
Big Data Hadoop Course
Post a Comment