Need only color format on cell with out condition format

sksanjeev786

Well-known Member
Joined
Aug 5, 2020
Messages
873
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Team,

I have applied the condition format and after that I need that color on each cells even I remove the condition format.

223413583_EA_Games_APEX_CXL_IT_Q1_2022_WITH_DELTA_TOC.xlsx
ABCDEFGHIJKLMNO
9Base: Total
10
11
12BaseFacebook ControlFacebook Ad ADelta (B-A)YouTube ControlYouTube Ad ADelta (D-C)Xbox ControlXbox Ad ADelta (F-E)Facebook+YouTube ComboDelta (G-A)Delta (G-C)
13Unweighted Base72312551-7413215523136124-122068174
14
15Base72312551-7413215523136124-122068174
16
17Effective Base64312522-10313214614136113-231492417
18
19Unaided Brand Awareness32.525.612.1-13.539.438.7-0.730.934.63.732.16.5-7.3
20
21Aided Awareness42.834.445.511.144.740.1-4.644.949.14.241.47.0-3.3
22
23Brand Favorability23.616.828.011.222.722.0-0.825.030.35.323.56.70.7
24
25Purchase Intent24.420.828.07.220.521.51.130.128.0-2.123.12.32.7
26
27Online Ad Awareness19.716.835.318.514.417.93.519.924.14.322.25.47.8
28
29Message Association15.112.015.33.39.115.76.615.423.27.715.63.66.5
30
31Affinity10.711.213.72.56.87.20.413.214.81.68.8-2.42.0
32
T63
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L31Expression=FIND('T62'!E$13,'T62'!F43)textNO
L31Expression=FIND('T62'!E$13,'T62'!F43)textNO
L29Expression=FIND('T62'!E$13,'T62'!F40)textNO
L29Expression=FIND('T62'!F$13,'T62'!E40)textNO
L27Expression=FIND('T62'!F$13,'T62'!E37)textNO
L27Expression=FIND('T62'!E$13,'T62'!F37)textNO
L25Expression=FIND('T62'!F$13,'T62'!E34)textNO
L25Expression=FIND('T62'!E$13,'T62'!F34)textNO
L23Expression=FIND('T62'!F$13,'T62'!E31)textNO
L23Expression=FIND('T62'!E$13,'T62'!F31)textNO
L21Expression=FIND('T62'!F$13,'T62'!E28)textNO
L21Expression=FIND('T62'!E$13,'T62'!F28)textNO
L19Expression=FIND('T62'!F$13,'T62'!E25)textNO
L19Expression=FIND('T62'!E$13,'T62'!F25)textNO
I31Expression=FIND('T62'!G$13,'T62'!F43)textNO
I31Expression=FIND('T62'!F$13,'T62'!G43)textNO
I29Expression=FIND('T62'!G$13,'T62'!F40)textNO
I29Expression=FIND('T62'!F$13,'T62'!G40)textNO
I27Expression=FIND('T62'!G$13,'T62'!F37)textNO
I27Expression=FIND('T62'!F$13,'T62'!G37)textNO
I25Expression=FIND('T62'!G$13,'T62'!F34)textNO
I25Expression=FIND('T62'!F$13,'T62'!G34)textNO
I23Expression=FIND('T62'!G$13,'T62'!F31)textNO
I23Expression=FIND('T62'!F$13,'T62'!G31)textNO
I21Expression=FIND('T62'!G$13,'T62'!F28)textNO
I21Expression=FIND('T62'!F$13,'T62'!G28)textNO
I19Expression=FIND('T62'!G$13,'T62'!F25)textNO
I19Expression=FIND('T62'!F$13,'T62'!G25)textNO
F31Expression=FIND('T62'!E$13,'T62'!D43)textNO
F31Expression=FIND('T62'!D$13,'T62'!E43)textNO
F29Expression=FIND('T62'!E$13,'T62'!D40)textNO
F29Expression=FIND('T62'!D$13,'T62'!E40)textNO
F27Expression=FIND('T62'!E$13,'T62'!D37)textNO
F27Expression=FIND('T62'!D$13,'T62'!E37)textNO
F25Expression=FIND('T62'!E$13,'T62'!D34)textNO
F25Expression=FIND('T62'!D$13,'T62'!E34)textNO
F23Expression=FIND('T62'!E$13,'T62'!D31)textNO
F23Expression=FIND('T62'!D$13,'T62'!E31)textNO
F21Expression=FIND('T62'!E$13,'T62'!D28)textNO
F21Expression=FIND('T62'!D$13,'T62'!E28)textNO
F19Expression=FIND('T62'!E$13,'T62'!D25)textNO
F19Expression=FIND('T62'!D$13,'T62'!E25)textNO
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
You have some unusual conditional format conditions in some of those cells (eg two identical expressions in cell L31) however, see if this does what you want.
Test with a copy of your workbook.

VBA Code:
Sub Fix_Color_Format()
  Dim c As Range
  
  For Each c In Range("C13", Range("O" & Rows.Count).End(xlUp))
    c.Interior.Color = c.DisplayFormat.Interior.Color
  Next c
End Sub
 
Upvote 0
You have some unusual conditional format conditions in some of those cells (eg two identical expressions in cell L31) however, see if this does what you want.
Test with a copy of your workbook.

VBA Code:
Sub Fix_Color_Format()
  Dim c As Range
 
  For Each c In Range("C13", Range("O" & Rows.Count).End(xlUp))
    c.Interior.Color = c.DisplayFormat.Interior.Color
  Next c
End Sub


thank you so much, Peter :):)
Work perfectly...
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top