Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 12-08-2018, 01:05 PM
AC PORTA VIA AC PORTA VIA is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Oct 2015
Posts: 19
AC PORTA VIA is on a distinguished road
Default 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
Attached Files
File Type: xlsx TEST.xlsx (13.5 KB, 10 views)

Last edited by AC PORTA VIA; 12-08-2018 at 04:46 PM.
Reply With Quote
  #2  
Old 12-08-2018, 08:06 PM
Kenneth Hobson Kenneth Hobson is offline Windows 10 Office 2016
Novice
 
Join Date: Jun 2018
Posts: 27
Kenneth Hobson is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 12-16-2018, 05:02 PM
AC PORTA VIA AC PORTA VIA is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Oct 2015
Posts: 19
AC PORTA VIA is on a distinguished road
Default

Thanks for your help Kenneth
Unfortunately i couldn't get anything to work the way i wanted
Reply With Quote
  #4  
Old 12-17-2018, 08:26 PM
Guessed's Avatar
Guessed Guessed is offline Windows 10 Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,119
Guessed has a spectacular aura aboutGuessed has a spectacular aura aboutGuessed has a spectacular aura about
Default

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
Reply With Quote
  #5  
Old 12-19-2018, 04:01 PM
AC PORTA VIA AC PORTA VIA is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Oct 2015
Posts: 19
AC PORTA VIA is on a distinguished road
Default

Thank you for your help
Works great
Reply With Quote
Reply

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


All times are GMT -7. The time now is 01:04 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft