#1
|
|||
|
|||
Cell text color vs cell background color
Hi to all
I have one column with a lot of rows with DIFFERENT background COLOR but text color is same for all which makes some of rows hard to read (some rows background color and text color is almost the same) Need vba code to change text color based on cell background color to be able to read text different colors in rows are not conditional formatted Thanks EDIT BELOW attached file shows different colors in column I-These are actually duplicates values FYI-Colors are made by macro inserted in column M i hope i made it clear enough to understand Last edited by AC PORTA VIA; 12-08-2018 at 04:46 PM. |
#2
|
|||
|
|||
There are 57 interior colors. I guess you could add or subtract some value. Even so, some colors with an offset won't have enough contrast. I would suggest making an array with 0 to 56 elements with offset values that looks right to you.
You might want to review what your interiorindex colors are using this code. http://dmcritchie.mvps.org/excel/colors.htm |
#3
|
|||
|
|||
Thanks for your help Kenneth
Unfortunately i couldn't get anything to work the way i wanted |
#4
|
||||
|
||||
I think you are looking for a luminance value along the lines of https://stackoverflow.com/questions/...s-of-rgb-color
Expanding on that idea and making use of a function found here https://stackoverflow.com/questions/...color-property the basic concept with workings are... Code:
Sub ShowLuminance() Dim iR As Long, iG As Long, iB As Long, Colour As Long, iColour As Long Dim rng As Range, cel As Range, Cel2 As Range, FirstAddress As String Dim sRGB As String Set rng = Worksheets("SHEET1").Range("I2:I" & Range("I" & Rows.Count).End(xlUp).Row) For Each cel In rng sRGB = Color(cel) cel.Offset(0, 1).Value = sRGB cel.Offset(0, 2).Value = Luminance(sRGB) If cel.Offset(0, 2).Value > 128 Then cel.Font.Color = RGB(0, 0, 0) Else cel.Font.Color = RGB(255, 255, 255) End If Next End Sub Function Color(rng As Range, Optional formatType As Integer = 2) As Variant Dim colorVal As Variant colorVal = Cells(rng.Row, rng.Column).Interior.Color Select Case formatType Case 1 Color = Hex(colorVal) Case 2 Color = (colorVal Mod 256) & "," & ((colorVal \ 256) Mod 256) & "," & (colorVal \ 65536) Case 3 Color = Cells(rng.Row, rng.Column).Interior.ColorIndex Case Else Color = colorVal End Select End Function Function Luminance(str) As Long Dim iR As Integer, iG As Integer, iB As Integer, iRGB As Variant iRGB = Split(str, ",") Luminance = (0.299 * iRGB(0) + 0.587 * iRGB(1) + 0.114 * iRGB(2)) End Function
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#5
|
|||
|
|||
Thank you for your help
Works great |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Set color and background of special bullet in table cell | kkkwj | Word VBA | 2 | 05-08-2018 08:40 AM |
Allow Cell Background Fill/Text Color Change while Protected Sheets are Grouped | RaudelJr | Excel | 5 | 04-18-2017 11:11 PM |
Remove white text background (keeping the font color and page color intact | cc3125 | Word | 1 | 10-26-2015 06:44 PM |
VBA Table – Search All Tables - Find & Replace Text in Table Cell With Specific Background Color | jc491 | Word VBA | 8 | 09-30-2015 06:10 AM |
Cell Background Color: Base it on Content of Cell? | tatihulot | Excel | 4 | 08-14-2013 03:24 PM |