VBA help: need all color cells in font with bold

[sksanjeev786]

Hi Team,

Below are the row data and i need the same font color with Bold where ever we have cell color and once it is done i dont want cell color only font color is fine for me
Application only for Green ,Red and Orange
can we add + symbol before the number as - are already available

Book1
	A	B	C	D	E	F	G	H
1	Product Awareness: Airbnb Categories	47%	4.0	-0.5	2.6	3.2	5.8	-3.7
2	BENEFITS: Helps me find the perfect trip	49%	8.2	1.7	2.6	1.8	0.6	0.4
3	INSPIRATION: Is a great way to discover new places	64%	6.7	3.7	7.1	5.1	5.2	-1.6
4	BENEFITS: Helps me experience the world in a different way	54%	8.3	5.4	4.5	3.9	2.7	-2.9
5	BENEFITS: Is good for a variety of trips	57%	4.9	4.5	4.4	3.1	1.1	-8.0
6	INSPIRATION: Offers a unique way to travel	61%	5.8	3.0	5.6	3.4	3.1	0.1
7	INSPIRATION: Makes me want to travel	56%	7.8	4.5	5.9	4.9	5.0	7.1
8	Brand Metrics
9	Product Awareness: Aircover (End-to-end protection for Airbnb stays)	10%	4.2	0.7	-0.1	-0.4	-2.3	3.6
10	BENEFITS: I'd trust Airbnb to help me if something goes wrong (i.e. last-minute cancellation, issues with my accommodations, etc.)	48%	7.7	3.1	1.8	-0.1	0.1	-1.4
11	BENEFITS: Is reliable	57%	8.0	3.8	6.0	3.8	1.4	-1.7
12	BARRIERS: Feels risky	25%	0.0	-4.7	-7.3	-6.6	-5.9	-5.6
Sheet2

 

[Georgiboy]

You could do the colour part with conditional formatting if we can understand what denotes:
Green
Orange
Red
Nothing

Or has this part been done manually.

The format with the + before the number can be done using the custom format of: +0.0;-0.0;0

 

[Georgiboy]

The code to change the colours would look something like below although it will make the cells hard to read in the current state.

Sub test()
    Dim rng As Range, rCell As Range
    
    Set rng = Range("A1").CurrentRegion
    Set rng = rng.Offset(, 2).Resize(, 6)
    
    For Each rCell In rng.Cells
        If rCell.Interior.ColorIndex <> 15 Then
            rCell.Font.ColorIndex = rCell.Interior.ColorIndex
            rCell.Interior.ColorIndex = 0
        End If
    Next rCell
End Sub

 

[sksanjeev786]

You could do the colour part with conditional formatting if we can understand what denotes:
Green
Orange
Red
Nothing

Or has this part been done manually.

The format with the + before the number can be done using the custom format of: +0.0;-0.0;0

Hi Geo,

I need the same font color as then one we have in cell color in excel i don't think we can do via manually

if yes can you please help me with this

 

[sksanjeev786]

The code to change the colours would look something like below although it will make the cells hard to read in the current state.

Sub test()
    Dim rng As Range, rCell As Range
  
    Set rng = Range("A1").CurrentRegion
    Set rng = rng.Offset(, 2).Resize(, 6)
  
    For Each rCell In rng.Cells
        If rCell.Interior.ColorIndex <> 15 Then
            rCell.Font.ColorIndex = rCell.Interior.ColorIndex
            rCell.Interior.ColorIndex = 0
        End If
    Next rCell
End Sub

Hi Geo,

Thank you so much for your help on this

I got perfect solution but can we have bold with + symbol on positive number where ever we got font color

 

[Georgiboy]

If you want the macro to do it all then maybe:

Sub test()
    Dim rng As Range, rCell As Range
  
    Set rng = Range("A1").CurrentRegion
    Set rng = rng.Offset(, 2).Resize(, 6)
  
    For Each rCell In rng.Cells
        With rCell
            If .Interior.ColorIndex <> 15 Then
                .Font.ColorIndex = rCell.Interior.ColorIndex
                .Interior.ColorIndex = 0
                .Font.Bold = True
                .NumberFormat = "+0.0;-0.0;0.0"
            End If
        End With
    Next rCell
End Sub

 

[sksanjeev786]

If you want the macro to do it all then maybe:

Sub test()
    Dim rng As Range, rCell As Range
 
    Set rng = Range("A1").CurrentRegion
    Set rng = rng.Offset(, 2).Resize(, 6)
 
    For Each rCell In rng.Cells
        With rCell
            If .Interior.ColorIndex <> 15 Then
                .Font.ColorIndex = rCell.Interior.ColorIndex
                .Interior.ColorIndex = 0
                .Font.Bold = True
                .NumberFormat = "+0.0;-0.0;0.0"
            End If
        End With
    Next rCell
End Sub

Super cool

Thank you so much, Geo... your and team support always help us to work better in our lifestyle

 

[sksanjeev786]

If you want the macro to do it all then maybe:

Sub test()
    Dim rng As Range, rCell As Range
 
    Set rng = Range("A1").CurrentRegion
    Set rng = rng.Offset(, 2).Resize(, 6)
 
    For Each rCell In rng.Cells
        With rCell
            If .Interior.ColorIndex <> 15 Then
                .Font.ColorIndex = rCell.Interior.ColorIndex
                .Interior.ColorIndex = 0
                .Font.Bold = True
                .NumberFormat = "+0.0;-0.0;0.0"
            End If
        End With
    Next rCell
End Sub

Hi Geo,

Sorry for the multiple treads

just now i got an update from my team i need only red and green font to be bold and need to ignore the Orange color in excel
so for the orange color, i need the same grey pattern with white font we have in Excel.

Thank you in advance

 

[Georgiboy]

Hi Geo,

Sorry for the multiple treads

just now i got an update from my team i need only red and green font to be bold and need to ignore the Orange color in excel
so for the orange color, i need the same grey pattern with white font we have in Excel.

Thank you in advance

Maybe:

Sub test()
    Dim rng As Range, rCell As Range
 
    Set rng = Range("A1").CurrentRegion
    Set rng = rng.Offset(, 2).Resize(, 6)
 
    For Each rCell In rng.Cells
        With rCell
            If .Interior.ColorIndex = 43 Or .Interior.ColorIndex = 18 Then
                .Font.ColorIndex = rCell.Interior.ColorIndex
                .Interior.ColorIndex = 0
                .Font.Bold = True
                .NumberFormat = "+0.0;-0.0;0.0"
            ElseIf .Interior.ColorIndex = 44 Then
                .Interior.ColorIndex = 15
                .Font.Color = vbWhite
            End If
        End With
    Next rCell
End Sub

 

[sksanjeev786]

Maybe:

Sub test()
    Dim rng As Range, rCell As Range
 
    Set rng = Range("A1").CurrentRegion
    Set rng = rng.Offset(, 2).Resize(, 6)
 
    For Each rCell In rng.Cells
        With rCell
            If .Interior.ColorIndex = 43 Or .Interior.ColorIndex = 18 Then
                .Font.ColorIndex = rCell.Interior.ColorIndex
                .Interior.ColorIndex = 0
                .Font.Bold = True
                .NumberFormat = "+0.0;-0.0;0.0"
            ElseIf .Interior.ColorIndex = 44 Then
                .Interior.ColorIndex = 15
                .Font.Color = vbWhite
            End If
        End With
    Next rCell
End Sub

Waw...!!! Thanks, youuuuu so much Geo for your hard work and your support on this

This made my day..........