Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-08-2018, 01:05 PM
AC PORTA VIA
Guest
 
Posts: n/a
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, 16 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 Cell text color vs cell background color Windows 10 Cell text color vs cell background color Office 2016
Advanced Beginner
 
Join Date: Jun 2018
Posts: 37
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
Guest
 
Posts: n/a
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 Cell text color vs cell background color Windows 10 Cell text color vs cell background color Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
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
Guest
 
Posts: n/a
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
Cell text color vs cell background color Remove white text background (keeping the font color and page color intact cc3125 Word 1 10-26-2015 06:44 PM
Cell text color vs cell background color 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 text color vs cell background color Cell Background Color: Base it on Content of Cell? tatihulot Excel 4 08-14-2013 03:24 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:51 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft