VBA Need cell color in font color

[sksanjeev786]

Hi Team,

I need the cell color on the data........

Book1
	A	B	C	D	E	F	G
2	Unaided Brand Awareness	8%	9%	0%	9%	7%	-2%
3	Aided Brand Awareness	94%	95%	1%	91%	97%	6%
4	Message Association	18%	19%	1%	17%	22%	5%
5	Consideration Intent	45%	57%	12%	41%	69%	28%
6	Aided Brand Awareness	85%	88%	3%	86%	88%	2%
7	Total Ad Recall	47%	56%	9%	42%	71%	29%
8	Digital Ad Recall	21%	23%	2%	22%	49%	27%
Sheet1

 

[Wookiee]

I'm guessing at what you want, as your request was a little vaguely worded. But by your title, I think you want to hide the values in cells by making the font the same color as the interior. If that is the case, this should work:

Sub SomethinForSanjeev()

Dim rngCell As Range

For Each rngCell In Selection

  With rngCell

    .Font.Color = .Interior.Color

  End With

Next rngCell

End Sub

 

[sksanjeev786]

I'm guessing at what you want, as your request was a little vaguely worded. But by your title, I think you want to hide the values in cells by making the font the same color as the interior. If that is the case, this should work:

Sub SomethinForSanjeev()

Dim rngCell As Range

For Each rngCell In Selection

  With rngCell

    .Font.Color = .Interior.Color

  End With

Next rngCell

End Sub

Hi Wookie,

Thank you so much for your time on this

I need to change the font color based on the cell color we have and once font color changes (Green, Orange and Red Font data) then Cell background white is fine for me

Let me know if you need more update on this
Regards
Sanjeev

 

[Wookiee]

In that case, this version worked for me (you will likely need to tweak depending on the actual color values of your cells).

Sub SomethinForSanjeevPt2_ElectronicBoogaloo()

Dim rngCell As Range

For Each rngCell In Selection

  With rngCell

    Select Case rngCell.Interior.Color

      'You'll need to figure out how best to identify the cell colors
      Case Is = vbRed, vbGreen, rgbOrange
      
        .Font.Color = .Interior.Color
        .Interior.Pattern = xlNone

    End Select

  End With

Next rngCell

End Sub

 

[sksanjeev786]

In that case, this version worked for me (you will likely need to tweak depending on the actual color values of your cells).

Sub SomethinForSanjeevPt2_ElectronicBoogaloo()

Dim rngCell As Range

For Each rngCell In Selection

  With rngCell

    Select Case rngCell.Interior.Color

      'You'll need to figure out how best to identify the cell colors
      Case Is = vbRed, vbGreen, rgbOrange
     
        .Font.Color = .Interior.Color
        .Interior.Pattern = xlNone

    End Select

  End With

Next rngCell

End Sub

Hi Wookie,

Thank you so much for your help on this. This works perfectly for me.
But i have a few scenarios mentioned in the below points

1. Can I get the grey cell color as empty as how we have it in B1 to B7 font color?
2. For orange can I get the color as blue color (RGB=0,122,192)

Thanks in advance

Below codes i have added RGB for now

Sub SomethinForSanjeevPt2_ElectronicBoogaloo()

Dim rngCell As Range

For Each rngCell In Selection

With rngCell

Select Case rngCell.Interior.Color

'You'll need to figure out how best to identify the cell colors
Case Is = RGB(204, 51, 51), RGB(150, 193, 29), RGB(255, 150, 0)

.Font.Color = .Interior.Color
.Interior.Pattern = xlNone

End Select

End With

Next rngCell

End Sub


Regards
Sanjeev

 

[Joe4]

Sanjeev,

Please use Code Tags when posting code. Otherwise, it can be difficult/messy to read.
See: How to Post Your VBA Code

 

[sksanjeev786]

Sanjeev,

Please use Code Tags when posting code. Otherwise, it can be difficult/messy to read.
See: How to Post Your VBA Code

Thanks for checking on this joe Sure i will add it for further VBA codes

Looks like i can not edit above post

 

[Joe4]

Looks like i can not edit above post

Yes, you only have 10 minutes in which to edit a post.

 

[Wookiee]

Try this version:

Sub SomethinForSanjeev_3D()
'Crafted by Wookiee at MrExcel.com


Dim rngCell As Range

For Each rngCell In Selection

  With rngCell

    Select Case rngCell.Interior.Color

      Case Is = RGB(204, 51, 51), RGB(150, 193, 29), RGB(191, 191, 191)
      
        .Font.Color = .Interior.Color
        .Interior.Pattern = xlNone

      Case RGB(255, 150, 0)
      
        .Font.Color = RGB(0, 122, 192)
        .Interior.Pattern = xlNone

    End Select

  End With

Next rngCell

End Sub

 

[sksanjeev786]

Try this version:

Sub SomethinForSanjeev_3D()
'Crafted by Wookiee at MrExcel.com


Dim rngCell As Range

For Each rngCell In Selection

  With rngCell

    Select Case rngCell.Interior.Color

      Case Is = RGB(204, 51, 51), RGB(150, 193, 29), RGB(191, 191, 191)
     
        .Font.Color = .Interior.Color
        .Interior.Pattern = xlNone

      Case RGB(255, 150, 0)
     
        .Font.Color = RGB(0, 122, 192)
        .Interior.Pattern = xlNone

    End Select

  End With

Next rngCell

End Sub

Wow!!!!!

Thank you so much for your hard work on this..

Super Happy!!!!!!