还是针对楼主最初染色的需求来说吧：
我虽然用VBA基本上达到了这个效果，但是始终感觉还是比较麻烦。不知道有没有更简便的办法，比如用公式？我对Excel公式不是很熟，熟悉的朋友可以再琢磨下。
用VBA实现的步骤如下：
1、新建一个Excel文件；
2、键入Alt+F11打开VBA编辑环境，然后选择菜单“插入 - 模块”，在代码编辑区粘贴如下代码：
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
UpdateSelection Target
End Sub
Private Sub UpdateSelection(oSelection As Range)
Dim oArea As Range
Dim nColumnIndex As Long, nMaxColumn As Long, nMaxRow As Long
Dim oColumnDict As Object
Dim vKey As Variant
Set oColumnDict = CreateObject("ScriptingDictionary")
' Collect all columns that need to be updated
For Each oArea In oSelectionAreas
nMaxColumn = Min(ActiveSheetUsedRangeColumn + ActiveSheetUsedRangeColumnsCount, oAreaColumn + oAreaColumnsCount - 1)
nMaxRow = Min(ActiveSheetUsedRangeRow + ActiveSheetUsedRangeRowsCount, oAreaRow + oAreaRowsCount - 1)
For nColumnIndex = oAreaColumn To nMaxColumn
oColumnDict(nColumnIndex) = nColumnIndex
Next
Next
For Each vKey In oColumnDictKeys
UpdateColumn CInt(vKey)
Next
End Sub
Private Sub UpdateColumn(nColumnIndex As Long)
Dim oFirstCell As Range, oLastCell As Range, oNextCell As Range, oCalculateBand As Range
Dim nBlankCellsCount As Long
Dim vCellColor As Variant
Set oFirstCell = Cells(1, nColumnIndex)
Set oLastCell = Cells(ActiveSheetUsedRangeRow + ActiveSheetUsedRangeRowsCount - 1, nColumnIndex)
' Firstly clear the interior color of the existing cells
With Range(oFirstCell, oLastCell)Interior
Pattern = xlNone
End With
Do While True
' Find the end cell of one single band (the next nonblank cell after the band start cell)
Set oNextCell = CellsFind(What:=nColumnIndex - 1, After:=Cells(oFirstCellRow, nColumnIndex), LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If oNextCell Is Nothing Then
' Not found, move to the end cell
Set oNextCell = oLastCell
ElseIf (oNextCellColumn <> nColumnIndex) Or (oNextCellRow <= oFirstCellRow) Then
' Not found after the band end cell in this column, also move to the end cell
Set oNextCell = oLastCell
Else
' Found, move to the cell right above it
Set oNextCell = Cells(oNextCellRow - 1, nColumnIndex)
End If
' Check to see if the start cell in the band needs to be included
If oFirstCellText = "" Then
' First cell is empty, should be counted
Set oCalculateBand = Range(oFirstCell, oNextCell)
Else
' First cell is not empty, skip it
Set oCalculateBand = Range(Cells(oFirstCellRow + 1, nColumnIndex), oNextCell)
End If
' Calculate the band size
nBlankCellsCount = oCalculateBandRowsCount
With oCalculateBandInterior
If nBlankCellsCount < 40 Then
Pattern = xlNone
ElseIf (nBlankCellsCount >= 40) And (nBlankCellsCount < 50) Then
Color = vbYellow
Else
Color = vbRed
End If
End With
' Move to the next band in this column
Set oFirstCell = Cells(oNextCellRow + 1, nColumnIndex)
' Check to see if reached the last cell
If oFirstCellRow >= oLastCellRow Then Exit Do
Loop
End Sub
Private Function Min(x As Long, y As Long)
Min = (x + y - Abs(x - y)) / 2
End Function
3、键入Alt+Q返回Excel主窗口，保存；
4、现在可以在当前工作簿里面试验一下，比如在第一列随机间隔输入若干0，检查两个相邻的0之间空格数量分别为小于40、40-50、50以上三种情况时，空格染色是否正确。在第二列、第三列、第四列……分别测试输入数字1、2、3、……的情况。
请注意，因为启用了宏，所以需要在“工具 - 宏 - 安全性”里面选择“中”或者“低”，并且在下次打开这个文档时要确认启用宏。
请楼主看看是不是自己想要的效果，如果有问题可以补充提问或者和我联系。
以下是一个可能的例子代码，其中假设条件工序代码在第1列，类产品在第2列，供应商级别在第3列，PPV值在第4列，并且数据的起始行为第2行。您可以根据您的实际情况进行相应的调整和修改。
```VBA
Sub sort_suppliers()
Dim ws As Worksheet
Dim lRow As Long
Dim FoundCell As Range
Dim strSearch As String
Dim sortRange As Range
'设置需要操作的工作表
Set ws = ThisWorkbookSheets("Sheet1")
'获取最后一行
lRow = wsCells(RowsCount, 1)End(xlUp)Row
'设置查找参数，如果需要可以将其改动为输入框让用户输入。
Dim cond_GXDM As String
cond_GXDM = "111" '条件工序代码
Dim cond_CPMC As String
cond_CPMC = "产品AB" '类产品
Dim cond_GYSJB As String
cond_GYSJB = "级别A" '供应商级别
'在第一图查找符合条件的单元格，将查找范围限制在前三列中
Set FoundCell = wsRange("A2:A" & lRow)Find(what:=cond_GXDM)
If Not FoundCell Is Nothing Then
Set FoundCell = wsRange("B" & FoundCellRow & ":D" & lRow)Find(what:=cond_CPMC & "," & cond_GYSJB)
If Not FoundCell Is Nothing Then
'找到相应行之后，利用Offset函数获取该供应商的PPV值
Set sortRange = FoundCellOffset(0, 1)Resize(1, 1)
'向下查找并选取符合条件的单元格，将其合并为一区域
Do Until IsEmpty(FoundCell) Or FoundCellOffset(0, -1) <> cond_GXDM
If FoundCellOffset(0, 1) = cond_CPMC And FoundCellOffset(0, 2) = cond_GYSJB Then
Set sortRange = ApplicationUnion(sortRange, FoundCellOffset(0, 3)Resize(1, 1))
End If
Set FoundCell = wsRange("B" & FoundCellRow + 1 & ":D" & lRow)Find(what:=cond_CPMC & "," & cond_GYSJB)
Loop
'按照PPV值从小到大排序
sortRangeSort key1:=sortRange, order1:=xlAscending, Header:=xlNo
Else
MsgBox "无符合条件的供应商。"
End If
Else
MsgBox "无符合条件的记录。"
End If
End Sub
```
此代码仅供参考，具体操作需要根据您的实际需求进行调整和修改。
Cell 179 , October 31, 2019 a 2019
Cellular Senescence: Defining a Path Forward
Definition and Characteristics of Cellular Senescence
Cellular senescence is a cell state
triggered by ：stressful insults and certain physiological processes,
characterized by ：aprolongedand generally irreversible cell-cycle arrest with secretory features, macromolecular damage , and altered metabolism
These features can be inter-dependent
Cell-Cycle Arrest
mammalian cells
retinoblastoma (RB) family and p53 proteins are important for establishing senescent cell-cycle arrest
RB1 and its family members p107 (RBL1) and p130 (RBL2) are phosphorylated by specific cyclin-dependent kinases (CDKs; CDK4, CDK6, CDK2)
This phosphorylation reduces the ability of the RB family members torepressE2F family transcription factor activity, which is required for cell-cycle progression
CDK2 inhibitor p21WAF1/Cip1 ( CDKN1A ) and CDK4/6 inhibitor p16INK4A ( CDKN2A ) accumulate
Secretion
SASP的功能
the SASP reinforces and spreads senescence in autocrineandparacrinefashions
activates immune responses that eliminate senescent cells
SASP factors mediate developmental senescence
tissue plasticity
contribute to persistent chronic inflammation (known as inflammaging)
the SASP can recruit immature immune-suppressive myeloid cells to prostate and liver tumors （肝脏肿瘤！！！看一下Distinct Functions of Senescence-Associated Immune Responses in Liver Tumor Surveillance and Tumor Progression
）
控制SASP的相关通路
enhancer remodeling
activation of transcription factors, such as NF-kB, C/EBPb, GATA4
mammalian target of rapamycin (mTOR)
p38MAPK signaling pathways
上游因子控制SASP的方式不同并且和老化诱导的途径有关
type 1 interferon response：DNA damage, cytoplasmic chromatin fragments (CCFs)
inflammasome ：damage-associated molecular patterns (DAMPs)
SASP的成分和强度不同决定于：duration of senescence, origin of the prosenescence stimulus, and cell type
SASP可以和外界的微环境通过：juxtacrine NOTCH/JAG1 signaling；release of ROS；cytoplasmic bridges；extracellular vesicles （egexosomes）
）
Macromolecular Damage
DNA Damage ：The first molecular feature associated with senescence wastelomere shortening, a result of the DNA end-replication problem , during serial passages
端粒导致DDR的过程
Type1
Telomere shortening during proliferation
telomeric DNA loop destabilization &telomere uncapping
generating telomere dysfunction-induced foci (TIFs)
ctivate the DDR
cell-cycle arrest
（This response can also be elicited by inhibiting or altering genes involved in telomere maintenance ）
Type2
oxidative DNA damage at telomeric G-reach repeats
telomere-associated foci (TAFs), can exist at telomeres （irrespective of telomere length or shelterin loss ）
DNA损伤种类
half of the persistent DNA damage foci in senescent cells localize to telomeres
other stressful subcytotoxic insults can trigger senescence by inducing irreparable DNA damage
genotoxic agents： radiation (ionizing and UV),
pharmacological agents (eg, certain chemotherapeutics),
oxidative stress
activated oncogenes can induce senescence (known as OIS) as a tumor-suppressive response, restricting the uncontrolled proliferation of potentially oncogenic cells
——OIS is often mediated by the tumor suppressors p16INK4 A and ARF , both encoded by theC DKN2A locus, imposing a cell-cycle arrest
DDR也可以产生OIS的激活
oncogene-driven hyperproliferation→damage signal originates at collapsed replication forks
DNA-SCARSs (DNA segments with chromatin alter- ations reinforcing senescence) ：Senescent cells harbor persistent nuclear DNA damage foci
特点：associate with promyelocytic leukemian (PML) nuclear bodies （？？？）
lack theDNA repair proteins RPA and RAD51 as well as single- stranded DNA ( ssDNA )
contain activated forms of the DDR mediators CHK2 and p53
DNA-SCARSs 可以调节细胞生长停滞和SASP，但是并不是一个老化细胞的特征
Protein Damage
Proteotoxicity is a hallmark of aging and cellular senescence
damaged proteins help identify senescent cells
蛋白质损伤的途径
ROS
↓
oxidize both methionine and cysteine residues
↓
alter protein folding and function
eg
（蛋氨酸和半胱氨酸，很多络氨酸磷酸酶都含有cys，因此他们所去磷酸化的部位就会被inactivate）PTPs：protein tyrosine phosphatases
↓
hyperactivating ERK signaling （similar to the effect of activated oncogenes ）
↓
trigger senescence
在肿瘤前期的病变处可以发现高磷酸化的ERK
Most protein oxidative damage：degradation by the ubiquitin proteasome system(UPS) orautophagy因此这两个特点可以作为检测老化情况的指标
promyelocytic leukemian (PML) 小体可以作为ROS和氧化损伤的sensor；同样也可以作为细胞老化的一个非特异性指标
Lipid Damage
老化细胞的清除可以减少老年小鼠肝脏和大脑中脂肪的沉积
（特定的脂代谢和老化相关知之甚少）
虽然很多方法可以检测脂肪在组织和细胞之前的变化，但是作为老化的biomarker还是未知的，因为不同的老化相关的脂肪十分不同。
比如在原癌基因激活的老化和复制性老化中脂肪代谢非常不同
Deregulated Metabolic Profile
Mitochondria
Mitochondria in senescent cells are less functional, showingdecreased membrane potential,increased proton leak, reduced fusion and fission rates, increased mass, andabundance of tricarboxylic acid (TCA) cycle metabolites
Altered AMP:ATP and ADP:ATP ratios during senescence
↓
activating AMPK (AMP-activated protein kinase), a main sensor of energy deprivation
↓
contribute to cell-cycle withdrawal
线粒体的功能失调和SASP的调节也相关
Mitophagy (mitochondrial clearance) in senescent cells appears tosuppressthe SASP
genetic or pharmacological inhibition of the ETC（electron transport chain ） can induce senescence even though cells lack expression of key pro-inflammatory SASP factors
NAD+/NADH ratios are reduced n senescent cells →alter the activity of poly-ADP ribose polymerase (PARP) and sirtuins （乙酰化酶）——both involved in activation of the SASP regulatorNF-kB
lysosomes
溶酶体的代谢是有细胞的代谢或者降解需要决定的
当 AA 在溶酶体的腔内含量比较高的时候，mTOR1 is recruited and activated
并且溶酶体可以和线粒相互关联
老化的细胞中溶酶体的数量和大小都有所增加，但是活性并没有增加
↓
lysosome-mitochondrial axis degrades
↓
decreased mitochondrial
↓
ncreases ROS production
↓
ROS targets cellular structures, including lysosomes, which forms a vicious feedback loop that induces more damage
溶酶体的数量和SA-b-gal 的活性有关
从治疗角度来讲，溶酶体增大的三部分可以为捕捉药物增加空间，比如CDK4/6 in- hibitors palbociclib哌柏西利, ribociclib, and abemaciclib
Senescence-Associated (Epi-)genetic and Gene Expression Changes
上述的表型变化大多是由于基因的表达所导致，这里我们解释下相关基因的情况
老化相关基因查询网址(http://Senequestnet)
Chromatin Landscape
表关遗传的变化大多和环境相关。replicative senescence has been correlated with global loss of DNA methylation at CpG sites ；Cells undergoing OIS fail to show such alterations in DNA methylation
老化的细胞呈现普遍的increase in chromatin accessibility, but the genome-wide profile varies depending on the stimulus
大多是组蛋白的变化individual histone modifications and variants
H4K16ac is often enriched at active pro- moters in senescent, but not proliferating, cells
N terminus proteolytic cleavage of H33 correlates with gene repression in a different subset of genes during senescence
Certain histone modifications are crucial for senescence, such as elevatedH4K20me3andH3K9me3,which contribute to the proliferation arrest ； elevatedH3K27acat gene enhancers promotes a SASP
Senescence is also associated with chromatinmorphological changes
Senescence-associated heterochromatin foci ( SAHF s ), visualized as DAPI-dense foci, are enriched in heterochromatin protein (HP) 1
SAHFs derive from chromatin factors—
including RB,
histone variant macroH2A,
high mobility group A proteins,
the HIRA/UBN1/CABIN1, a
ASF1a chaperones—and increased nuclear pore density
SAHF主要组成复制后基因的poor eterochromatic 部分，因此和细胞老化的相关性很小
细胞的老化同时也和大面积的H1丢失有关
enescence-associated distension of satellites ( SADSs ) ——先于SAHF，并且和细胞老化相关
Retrotransposable elements
repressed LINE-1 (L1) retrotransposons are activated, stimulating the cGAS-STING pathway that elicits a type 1 interferon response ，从而和SASP相关
Downregulation of lamin B1 （a major component of the nuclear lamina,）——也是老化细胞的一个主要特点
Lamin B1 和表观基因、enescence-associated chromatin structures (SAHFs and SADSs) 相关
主要发生在H3K9me3-rich regions：liberate H3K9me3 from the nuclear lamina promoting spatial rearrangement of H3K9me3 heterochromatin to form SAHFs 尤其是在OIS中发挥作用，然而复制性老化并没有什么相互作用
lamin B1 loss and reduced nuclear integrity 也可以促进SASP的形成
Transcriptional Signatures
genes linked to thecell-cycle arrestand SASPare frequently interrogated in combination with other biomarkers to validate the senescence phenotype or type of senescence
eg：CDKN1A ( p21WAF1/Cip1 ), CDKN2A ( p16INK4A ), and CDK2B (p15INK4B), and a subset of SASP genes, along with decreased expression of cyclins CCNA2 and CCNE2 and LMNB1 should be determined
为了更好研究转录情况——Whole-transcriptome studies，可以预测药物靶向；
但是现在的转录因子数据还是不足，因此相关转录因子的发现还是很重要的
miRNAs and Non-coding RNAs （）
尤其是miRNA和细胞老化的关联更大；可以直接或者间接的调节老化关键因子，
「p53, p21WAF1/Cip1, and SIRT1 」
negative：miR-504 targets the p53 30UTR, reducing p53 abundance and activity
Gld2-mediated stabilization of miR-122 enables its binding to the CBEP 30 UTR, resulting in decreased p53 mRNA polyadenylation and translation
multiple miRNAs downregu- late p21WAF1/Cip1, including 28 miRNAs that block OIS
miR-24 suppresses p16INK4a
positive：miR-605 targets MDM2, triggering p53-mediated senescence
miRNA feedback loops can modulate senescence programs
——For example, a p53/ miRNA/CCNA2 pathway drives senescence independently of the p53/p21WAF1/Cip1 axis ；p53- dependent upregulation of miR-34a/b/c downregulates cell proliferation and survival factors
「regulate the SASP 」
MiR-146a/b ：dampens a proinflammatory arm of the SAS
miRNAs also downregulate repressors of senescence, including polycomb group (PcG) members CBX7, EED, EZH2, and SUZ12 (miR-26b, 181a, 210, and 424), leading to p16INK4a derepression and senescence initiation
「the role of miRNAs in senes- cence extends beyond their classical functions」
Argonaute 2 ( AGO2 ) binds let-7f in the nucleus, forming a complex with RB1 (pRB ), resulting inrepressivechromatin at CDC2 and CDCA8 promoters Silencing these E2F target genes is required for senescence initiation
Long non-coding RNAs (lncRNAs) (>200 nt) canbind RNA, DNA, or proteinsto regulate senescence
ANRIL , a 30-40kb antisense transcript encoded by the CDKN2A locus, binds CBX7 torepress I NK4b/ARF/INK4a expression
lncRNA PANDA recruits PcG com- plexes, suppressing senescence-promoting genes
silencing of GUARDIN , a p53-responsive lncRNA, causes senescence or apoptosis
following OIS induced by RAF, the l ncRNA VAD preserves senescence by decreasing repressive H2AZ deposition at INK promoters
lncRNA UCA1 disrupts association of the RNA-binding protein hnRNP A1 with p16INK4A
Immune-Regulation and Anti-apoptotic Proteins
虽然细胞老化的诱导环境可以激活一些炎症反应因子，尤其是一些细胞表面的marker，可以作为研究从单个细胞到组织的方式，但是DCR2 and NKG2D ligands are not conserved among species, making mouse-to-human comparisons not possible
Notch1 in OIS and DPP4 in replicative and OIS：upregulated cell surface markers, 并且对于调节SASP有着一定的作用
increased expression of anti-apoptotic BCL-2 family members 也可以，因为老化的细胞是有凋亡抵抗的。
in Vivo Models to Study Cellular Senescence
Senescence Reporter Mice
通过报告基因estimate p16Ink4a expression
Murine Models of Accelerated Senescence and Aging
（各种早衰小鼠模型的建立方法）
Identification of Cellular Senescence In Vivo
A Simplified Algorithm for Detecting Senescent Cells In Situ
实验室免疫染色/WB+用显微镜观察下
不同方法联合检测
Challenges to Detect Senescent Cells in Humans
将细胞的老化和人类的疾病联系起来的
neurodegenerative disorders, glaucoma青光眼, cataract白内障， atherosclerosis and cardiovascular disease, diabetes, osteoarthritis, pulmonary, and renal and liver fibrosis
组织的检测可以：fresh samplesby SA-b-gal staining
or indirect markers in formalin-fixed tissues
histochemical dye SBB interacts with lipofuscin, another hallmark of senescent cells
Lipofuscin is preserved in fixed material
reagent (GL13) is amenable to immunohistochemistry （identified senescent Hodgkin and Reed-Sternberg (HRS) cells in Hodgkin lymphomas (cHL) where they predicted poor prognosis ）
Another method for identi- fying and quantifying senescent cells in vivo is SA-b-gal staining combined with ImageStream X analysis
We recommendcombining cytoplasmic (eg, SA-b-gal, lipofuscin), nuclear (eg, p16INK4A, p21WAF1/Cip1, Ki67) and SASP, context and/or cell-type-specific markers
老化相关仍然存在的问题
不可逆的细胞周期停滞并不一定存在所有的老化细胞中！
遗传学和表观遗传学在细胞老化中的关联？
在不同的老化诱导条件下细胞的修复方式又是什么？
抗衰老治疗的应用？
（我个人觉得mirNA&Non-coding RNAs是不是也挺热门的）
FunctionSumcolor(colAsRange,sumrangeAsRange)
DimicellAsRange
ApplicationVolatile
ForEachicellInsumrange
IficellInteriorColorIndex=colInteriorColorIndexThen
Sumcolor=ApplicationSum(icell)Sumcolor
EndIf
Nexticell
EndFunction
欢迎分享，转载请注明来源：品搜搜测评网
微信扫一扫
支付宝扫一扫
